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 .