CODEWITHSUNDEEP
mysqlselect
koubin
koubin

Reputation: 607

MySQL query to get item counts per month - displayed in row for each item

I have MySQL table like this (there will be more items in real scenario):

ID item date
1 a 2021-04-01
2 a 2021-04-05
3 a 2021-05-07
4 b 2021-07-09

And I want to create a table (the result will be displayed in HTML table) where each unique item name will have one row and also counts of the item for every month of whole year. So the result for the case above will look like:

item m1 m2 m3 m4 m5 m6 m7 m8 m9 m10 m11 m12
a 0 0 0 2 1 0 0 0 0 0 0 0
b 0 0 0 0 0 0 1 0 0 0 0 0

I would be able to do that by 2 separate SELECT queries where the second one would probably be in the for loop, but I can't figure out the way how to do that in one SELECT or one multiselect query, if that is even possible.

I got only that far, then got stucked:

SELECT MONTH(date) as m, COUNT(item) as c
FROM itemtable 
WHERE YEAR(date)=YEAR(CURRENT_DATE()) AND item="a" 
GROUP BY MONTH(date)

Upvotes: 0

Views: 32

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

It's a pivot operation. In MySQL I usually do it via conditional aggregation:

SELECT item,
  SUM(CASE WHEN MONTH(date) = 1 THEN 1 ELSE 0 END) as m1, 
  SUM(CASE WHEN MONTH(date) = 2 THEN 1 ELSE 0 END) as m2, 
  ...
  SUM(CASE WHEN MONTH(date) = 12 THEN 1 ELSE 0 END) as m12
FROM itemtable 
WHERE date BETWEEN '2021-01-01' and '2021-12-31'
GROUP BY item

To see how it works, remove the SUM/GROUP BY and look at the raw CASE WHEN - it splits the data across 12 columns in a diagonal, and then the sum/group squishes it to one row:

   A           A           A B C
   B     ->      B     ->
   C               C

I agree with strawberry's observation that this is often best handled in the app code rather than the DB, but as the data is fixed at "month 1" thru "month 12" it does make it relatively easier to do in the DB

Upvotes: 1

Related Questions