PIVOT/count sum of values in a column - Mysql

Question

I am new to MYSQL and trying to get PIVOT of my data. I have the sample table as below link:

create temporary table temp(reqtypeid int, reqcode int);
insert into temp(reqtypeid, reqcode) values (NULL,  0);
insert into temp(reqtypeid, reqcode) values (NULL,  2);
insert into temp(reqtypeid, reqcode) values ( 1  ,   0);
insert into temp(reqtypeid, reqcode) values (1 ,    1);
insert into temp(reqtypeid, reqcode) values (2   , NULL);
insert into temp(reqtypeid, reqcode) values ( 2  ,   0);
insert into temp(reqtypeid, reqcode) values (  2    ,    1);
insert into temp(reqtypeid, reqcode) values (  3    ,    1);
insert into temp(reqtypeid, reqcode) values ( 4 ,  NULL);
insert into temp(reqtypeid, reqcode) values ( 4  ,   1);

https://rextester.com/PVBI7963

My expected output is:

I am not intending to pivot but for every reqtypeid, I want to find the count of reqcode=null, reqcode=0/1/2. The code that I have tried is in the link. I am unable to get the correct output. Can someone help?

Consider handling issues of data display in application code — Strawberry
– Strawberry, Commented Nov 6, 2020 at 20:17

forpas · Accepted Answer · 2020-11-06 20:12:16Z

'NULL' is a string literal and not the same as NULL.
When comparing anything to NULL you must use the operator IS and not =.
Use conditional aggregation like this:

SELECT
    reqtypeid,  
    SUM(reqcode IS NULL) reqcode_null,
    SUM(reqcode = 0) reqcode_0,
    SUM(reqcode = 1) reqcode_1,
    SUM(reqcode = 2) reqcode_2
FROM temp
GROUP BY reqtypeid
ORDER BY reqtypeid IS NULL, reqtypeid

See the demo.
Results:

> reqtypeid | reqcode_null | reqcode_0 | reqcode_1 | reqcode_2
> --------: | -----------: | --------: | --------: | --------:
>         1 |            0 |         1 |         1 |         0
>         2 |            1 |         1 |         1 |         0
>         3 |            0 |         0 |         1 |         0
>         4 |            1 |         0 |         1 |         0
>      null |            0 |         1 |         0 |         1

Collectives™ on Stack Overflow

PIVOT/count sum of values in a column - Mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related