Vignesh Nagarajan Vignesh Nagarajan - 6 months ago 15
MySQL Question

increase count of three column based on specific value in single column in sql

I have a table with coulumns pkey and role

Table structure

I need the o/p table as below

O/p Table

The logic behind the table is if the role is configurator then count of config = 1 and count of review should increased by 1 and count of abstractor increased by 1

also if the role is reviewer then the count of reviewer should be 1 and count of abstractor should increased by 1.

I have tried the below code but cant derive the o/p.

SELECT lease_pkey,
SUM(CASE WHEN user_role IN( 'Abstractor') THEN 1 ELSE 0 END) AbstractorChagnes,
SUM(CASE WHEN user_role IN( 'Reviewer') THEN 1 ELSE 0 END) Reviewer,
SUM(CASE WHEN user_role IN( 'Project Owner TL','Configurator') THEN Reviewer = Reviewer+1 ELSE 0 END) AdminChanges
FROM v_audit_trail_trans
GROUP BY lease_pkey


please advice

Answer

You have to place the conditions for increasing reviewer and abstractor in their own counters:

SELECT lease_pkey, 
SUM(CASE WHEN user_role IN( 'Abstractor','Configurator','Reviewer') THEN 1 ELSE 0 END) AbstractorChagnes,
SUM(CASE WHEN user_role IN( 'Reviewer','Configurator') THEN 1 ELSE 0 END) Reviewer,
...
FROM v_audit_trail_trans
GROUP BY lease_pkey