Let's say I'm working with a single table that contains the following:
building_id brand_title product_title purchase_year
-----------------------------------------------------------------
2 Microsoft Surface Pro 3 2014
2 Microsoft Surface Pro 3 2014
2 Microsoft Surface Pro 4 2015
2 Apple iPad 2 2012
2 Apple iPad 2 2012
2 Apple iPad 2 2013
2 Apple iPad 2 2013
I have two queries that return the data I want, but I need to merge these queries into a single query. Here are the queries I have:
select brand_title, count(*) as brands_count from inventory
where building_id = 2
group by brand_title
order by brand_title
;
This returns:
brand_title brands_count
----------------------------
Microsoft 3
Apple 4
select brand_title, purchase_year, count(*) as total_by_purchase_year from inventory
where building_id = 2
group by purchase_year
order by brand_title
;
This returns:
brand_title purchase_year total_by_purchase_year
------------------------------------------------------
Microsoft 2014 2
Microsoft 2015 1
Apple 2012 2
Apple 2013 2
But what I need is a single query that gives me this as its output:
brand_title total_in_2012 total_in_2013 total_in_2014 total_in_2015 total_purchases_ever_by_brand
--------------------------------------------------------------------------------------------------------
Microsoft 0 0 2 1 3
Apple 2 2 0 0 4
Is this possible? I'm okay with using case statements or subqueries, but I just can't quite make this work!
