Mysql group by on two fields and count in SQL

Question

I have a table in my mysql db that has two columns: model and zone. See below.

Model   zone
Car 1   A
Car 2   B
Car 3   A
Car 4   C
Car 5   A
Car 1   A
Car 2   B
Car 3   B
Car 4   B
Car 5   A
Car 11  C
Car 12  A

I am trying to get the number of records for each unique model/Zone.

This is what I expect:

Model   A   B   C
Car 1   2   0   0
Car 2   0   2   0
Car 3   1   1   0
Car 4   0   1   1
Car 5   2   0   0
Car 11  0   0   1
Car 12  1   0   0

I want to get this count value of each model in each zone dynamically by mysql query.

Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — , Jul 25 '21 at 07:23

score 0 · Answer 1 · answered Jul 25 '21 at 09:29

0

use group by and case statement

select Model, 
       count(case when zone = 'A' then 1 end) as 'A',
       count(case when zone = 'B' then 1 end) as 'B',
       count(case when zone = 'C' then 1 end) as 'C'
from myTable
group by Model

demo in paiza.io

answered Jul 25 '21 at 09:29

Meysam Asadi

6,438
3
7
17

score 0 · Answer 2 · answered Jul 25 '21 at 14:40

You want to do conditional aggregation. For counting, MySQL has a very convenient short-hand syntax:

select Model, 
       sum(zone = 'A') as A,
       sum(zone = 'B') as B,
       sum(zone = 'C') as C
from t
group by Model;

However, if you want to do this dynamically, you have to create the SQL as a string, either using MySQL scripting, or in some application language such as Python. You can construct the dynamic part with an aggregation query:

select group_concat(replace('sum(zone = [zome]') as `zone`', '[zone]', zone) separator ', ')
from t
group by zone;

Mysql group by on two fields and count in SQL

2 Answers2