how to filter with join using mysql?

Question

Using MySQL 8.0: Here's my code:

WITH table1 AS (
SELECT MAX(CASE 
    WHEN a.A IS NULL THEN 4
    WHEN b.code IS NULL THEN 3
    ELSE a.A
    END  
) AS CODE,
    CASE 
        WHEN a.A IS NULL THEN 'BLANK' 
        WHEN b.code IS NULL THEN 'NOT VALID'
        ELSE b.description 
    END AS SEX,
COUNT(*) AS TOTAL ,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS PERCENT
FROM AA a LEFT JOIN BB b ON b.code = a.A where a.type = 1
GROUP BY SEX
)
SELECT CODE, SEX, TOTAL, PERCENT, 
    SUM(TOTAL) OVER (ORDER BY CODE) AS CUMULATIVE, 
    ROUND(100.0 * SUM(TOTAL) OVER (ORDER BY CODE) / SUM(TOTAL) OVER (), 2) AS CUMPERCENT
FROM table1
UNION ALL
SELECT 0, 'TOTAL', COUNT(*), 100.00, 0, 0.00
FROM AA
ORDER BY CODE;

CODE | SEX        | TOTAL | PERCENT | CUMULATIVE | CUMPERCENT
0      TOTAL        11      100.00    0            0.00
1      Male         2       33.33     2            33.33
2      Female       2       33.33     4            66.67
3      NOT VALID    1       16.67     5            83.33
4      BLANK        1       16.67     6            100.00

Desired output:

CODE | SEX        | TOTAL | PERCENT | CUMULATIVE | CUMPERCENT
0      TOTAL        6       100.00    0            0.00
1      Male         2       33.33     2            33.33
2      Female       2       33.33     4            66.67
3      NOT VALID    1       16.67     5            83.33
4      BLANK        1       16.67     6            100.00

The problem is The total should be 6. How will I filter items with type = 1 only in table AA?

Here's my sample database:

CREATE TABLE AA (
    A int,
    type int
);

insert into AA (A,type) values (1,1);
insert into AA (A,type) values (1,1);
insert into AA (A,type) values (2,1);
insert into AA (A,type) values (2,1);
insert into AA (A,type) values (3,1);
insert into AA (A,type) values (NULL,1);
insert into AA (A,type) values (1,2);
insert into AA (A,type) values (1,2);
insert into AA (A,type) values (1,2);
insert into AA (A,type) values (2,2);
insert into AA (A,type) values (2,2);

CREATE TABLE BB (
    code int,
    description varchar(30)
);

insert into BB (code, description) values (1, 'Male');
insert into BB (code, description) values (2, 'Female');

THANK YOU.

user_6248528 · Answer

Since you want to add a "total"-row depending on the content of your cte, you can just reuse it:

UNION ALL
SELECT 0, 'TOTAL', SUM(TOTAL), 100.00, 0, 0.00
FROM table1
ORDER BY CODE;

sums the totals of the rows of table1 (which are the rows with code 1 to 4 and thus the rows you are showing).

If you, as stated, actually want to filter AA directly, to get and count the same rows as in your cte, you have to redo the filter you did in your cte:

UNION ALL
SELECT 0, 'TOTAL', COUNT(*), 100.00, 0, 0.00
FROM AA a LEFT JOIN BB b ON b.code = a.A where a.type = 1
ORDER BY CODE;

This requires MySQL to evaluate this a second time though, so you may not actually want to do that.

how to filter with join using mysql?

1 Answers