CODEWITHSUNDEEP
mysqlgroup-bysumaggregate-functions
trojan01
trojan01

Reputation: 11

How to get SUM and COUNT data each Month?

For example I have data on

| S | Q |   Date   |
|===|===|==========|
| A | 2 |2020-11-23|
| B | 5 |2020-11-24|
| A | 1 |2020-12-01|
| B | 3 |2020-12-02|
| A | 2 |2020-12-03|
| B | 1 |2020-12-14|
| B | 2 |2021-01-02|

How to query if I want to get result like this v

| S | T | tQ | Last Date |
|===|===|====|===========|
| B | 1 |  2 | 2021-01-02|
| B | 2 |  4 | 2020-12-14|
| A | 2 |  3 | 2020-12-03|
| B | 1 |  5 | 2020-11-24|
| A | 1 |  2 | 2020-11-23|

T : count(S) in each month
tQ: sum(Q) in each month

Upvotes: 1

Views: 376

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Hmmm . . . this just looks like aggregation:

select max(date), s, count(*) as t, sum(q)
from t
group by year(date), month(date), s;

Upvotes: 1

Related Questions