mysql count multiple items per day

Question

I am looking to have a result set, with DATE, count of item1, count of item2. All from same table. Counting same column of data.

Example Output:

DATE | Oranges   | Apples
3-28 | 4  | 5
3-29 | 3  | 2

Logs are stored:

id, item, date
1 | Orange |  3-28
2 | Apple  |  3-28
2 | Apple  |  3-29
1 | Orange  |  3-29

I tried using a 2nd group by, but not sure if its counting correctly.

Concept?:

Select count(item) as apples, count(item) as orange, date(date_entered) FROM table GROUP by date(date_entered)

Answer 1

What you're asking for is known as a "pivot query". To accomplish it in MySQL, you may use a SUM() aggregate with a condition inside which returns 1 or 0 if the type of fruit is matched:

SELECT
  date,
  /* CASE condition returns 1 if the string matches, 0 otherwise
     and those are added up to get the count of matching rows */
  SUM(CASE WHEN item = 'Apple' THEN 1 ELSE 0 END) AS apples,
  SUM(CASE WHEN item = 'Orange' THEN 1 ELSE 0 END) AS oranges
FROM `table`
/* Apply the aggregate group over `date` */
GROUP BY date

Note that this is the general pattern if you know exactly the number of output columns to return. If you do not know all the possible values (and hence columns) ahead of time, you need to generate the list of SUM(CASE...) dynamically.