How to count results within results in MySQL?

Question

I have a table of items which I'm getting from a few different online stores, like Ebay/Amazon, etc. Before today, I wanted to simply group each item based on the year it was made and the manufacturer who made it.

So, the results would look something like this

total  year  manufacturer
100    1999  man_a
32     2002  man_b

Now, I want to narrow down my counts by what store each item is coming from. So all in all I want to know how many items were made in a certain year and by a certain manufacturer, as well as how many were found on ebay, amazon, etc. The results should look similar to this (at least provide the same data)

total  year  manufacturer  ebay_count  amazon_count
100    1999  man_a         40          60
32     2002  man_b         21          11

Can I do something like this, and if so how?

Heres the query I used to group the year and manufacturer, if its needed.

SELECT `year`, `manufacturer`, COUNT(id) AS `total` FROM `items` GROUP BY `manufacturer`, `year` ORDER BY `total` DESC LIMIT 10;

thanks

Answer 1

Use:

  SELECT `year`, 
         `manufacturer`, 
         COUNT(id) AS `total`,
         SUM(CASE WHEN store = 'ebay' THEN 1 ELSE 0 END) AS ebay_count,
         SUM(CASE WHEN store = 'amazon' THEN 1 ELSE 0 END) AS amazon_count
    FROM `items` 
GROUP BY `manufacturer`, `year` 
ORDER BY `total` DESC 
   LIMIT 10;