Select distinct rows, convert them into columns and count value

Question

I have a problem with extracting data from one table.

I have a table, t_stat, as follows:

id, country, instId, date
 1, Belgium,      1, 2014-04-06  
 2, Germany,      2, 2013-05-07  
 3, Italy,        3, 2018-06-08

What I need as a result: i need to pick up every distinct country, and count how much installs it has on each date. It should look like this:

Date        Belgium  Germany Italy
2013-05-07  0        1       0
2014-04-06  1        0       0
2018-06-08  0        0       1

I know, result can be achieved with pivot, but I can not construct query.

Thanks in advance.

Consider handling issues of data display, such as this, in application code — Strawberry, Dec 10 '18 at 11:31
Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Madhur Bhaiya, Dec 10 '18 at 11:38

score 0 · Answer 1 · answered Dec 10 '18 at 12:49

0

This should work

SELECT
    date,
    SUM( country = 'Belgium' ) AS Belgium,
    SUM( country = 'Germany' ) AS Germany,
    SUM( country = 'Italy' ) AS Italy
FROM t_stat
GROUP BY date

answered Dec 10 '18 at 12:49

DJo

2,133
4
30
46

This would work perfectly, but I fotgot to add that I need dynamic pivot because country column will be editting all the time. – Maksim Aristov Dec 10 '18 at 12:53

Select distinct rows, convert them into columns and count value

1 Answers1