Need to do TOTAL across a intersection in mysql

53 views Asked by At

I am using a opensource tool which do simple analytics. I have a mysql table for my analytics application.

I have a table say TBC which has has fields:

TBC table:

date                  amount           product
2020-05-18 17:23:44   400               A
2020-05-19 17:23:44   500               B
2020-06-08 17:23:44   100               A
2020-06-25 17:23:44   700               C
2020-06-18 17:23:44   200               B
2020-07-09 17:23:44   800               C
2020-07-19 17:23:44   900               A

I've written this query:

SELECT DATE_FORMAT(date, '%M,%Y') AS `date`,
   `TBC`.`product` AS `revenue`,
   count(*) AS `count`
FROM `TBC`
GROUP BY str_to_date(concat(date_format(`TBC`.`date`, '%Y-%m'), '-01'), '%Y-%m-%d'),
         `TBC`.`revenue`
ORDER BY str_to_date(concat(date_format(`TBC`.`date`, '%Y-%m'), '-01'), '%Y-%m-%d') DESC, `tbc`.`revenue` ASC

This gives me:

revenue       July,2020  June,2020  May,2020
A              900        100        400
B               --        200        500
C              800        700         --

I now want to have this below one:

revenue       July,2020  June,2020  May,2020
A              900        100        400
B               --        200        500
C              800        700         --
TOTAL          1700       1000       900

I know I need to use the SUM function but not sure which field I have to use .

0

There are 0 answers