How to group records displaying total value according to 2 fields of sql table?

Question

I have a table "sales" with the following structure:

-----------------------------------------
|  id |    date    | value |  category  |
-----------------------------------------
|  1  | 2018-06-10 | 30.00 |   cat1     |
-----------------------------------------
|  2  | 2018-07-13 | 40.00 |   cat3     |
-----------------------------------------
|  3  | 2018-07-17 | 50.00 |   cat1     |
-----------------------------------------
|  4  | 2018-08-14 | 35.00 |   cat3     |
-----------------------------------------
|  5  | 2018-06-20 | 15.00 |   cat2     |
-----------------------------------------
|  6  | 2018-08-05 | 25.00 |   cat2     |
-----------------------------------------
|  7  | 2018-07-09 | 30.00 |   cat4     |
-----------------------------------------
.....
-----------------------------------------
.....

I need to get the total sales value in a php array, grouped by month/year and category, such as: (following: year-month, total cat1, total cat2, total cat3, total cat4)

[
  [ "2018-06", 30, 15, 0, 0 ],
  [ "2018-07", 50, 0, 40, 30 ],
  [ "2018-08", 0, 25, 35, 0 ]
]

I tried with the sql below, and in other ways, but it did not work:

SELECT  
        `category`,
        SUM( `value` ) AS total,
        DATE_FORMAT( `date`, '%Y-%m' ) as date

    FROM 
        `sales`

    GROUP BY
        category, MONTH( `date` ), YEAR( `date` )

    ORDER BY
        `date` ASC

Details: The products are organized into four categories: cat1, cat2, cat3 and cat4. I need to know, in each month, what the total sales value that each category had.

Added mysql tag based on the dreaded backticks and date_format() — a_horse_with_no_name

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-06-04T13:31:10

You need to pivot the sum on the category:

SELECT  
    SUM(CASE WHEN category = 'cat1' THEN value END) AS sum1,
    SUM(CASE WHEN category = 'cat2' THEN value END) AS sum2,
    SUM(CASE WHEN category = 'cat3' THEN value END) AS sum3,
    SUM(CASE WHEN category = 'cat4' THEN value END) AS sum4,
    DATE_FORMAT(date, '%Y-%m') AS date
FROM sales
GROUP BY
    DATE_FORMAT(date, '%Y-%m')
ORDER BY
    DATE_FORMAT(date, '%Y-%m');

The basic idea behind that sum of CASE expressions used above is that we conditionally sum the value, for each month/year, depending on what the category is. This allows us to generate separate sums for each month/year and category.

How to group records displaying total value according to 2 fields of sql table?

1 Answers