PIVOT/count sum of values in a column - Mysql

debugcn 投稿 Dev

Rick

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?

forpas

'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

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]