CODEWITHSUNDEEP
mysqlsqlcountpivot
daniellalasa
daniellalasa

Reputation: 529

Count values that exist in another table and pivot the result

I want to count values from Chat table : 

ID    REASON_ID    DEPARTMENT_ID    
 1      46           1
 2      46           1
 3      50           1
 4      50           2
 5      100          1 
 6      100          2

Those exist in Reason table : 

ID    REASON_NAME
46    Reason1
50    Reason2
100   Reason3

Where DEPARTMENT_ID=1, and I want result like this :

ID46  ID50  ID100
 2      1     1

How can I do it?

Upvotes: 4

Views: 84

Answers (2)

Stidgeon
Stidgeon

Reputation: 2723

The dynamic SQL solution is way better, but if you want another option:

SELECT SUM(I46) ID46,
SUM(I50) ID50,
SUM(I100) ID100
FROM
(SELECT 
COUNT(CASE WHEN reason_id = 46 THEN 1 END) I46,
COUNT(CASE WHEN reason_id = 50 THEN 1 END) I50,
COUNT(CASE WHEN reason_id = 100 THEN 1 END) I100
FROM chat
WHERE department_id = 1
GROUP BY reason_id) q1;

Upvotes: 1

potashin
potashin

Reputation: 44581

You can try the following:

set @sql = null;
select group_concat( distinct
  concat( ' sum(r.id= ', r.id,') as ID', r.id )
) into @sql
from Chat c
join Reason r on c.reason_id = r.id 
where c.department_id = 1;

set @sql = concat('select ',@sql, ' 
from Chat c
join Reason r on c.reason_id = r.id 
where c.department_id = 1');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

SQLFiddle

Upvotes: 2

Related Questions