How will I fix this table using MySQL?

Question

I am new to MySQL. I am using MySQL 8.0.

My schema and sample data is as follows:

CREATE TABLE AA (
    A int
);

insert into AA (A) values (1);
insert into AA (A) values (1);
insert into AA (A) values (2);
insert into AA (A) values (2);
insert into AA (A) values (1);
insert into AA (A) values (3);

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

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

Here's my code

with totalcount as (
    select code as 'CODE',
        description as 'SEX',
        count(A) AS 'TOTAL',
        ROUND((COUNT(A) * 100.0) / (SELECT COUNT(A) FROM AA),2) AS 'PERCENT',
        sum(count(A)) over (order by CODE asc) AS 'CUMULATIVE',
        ROUND((SUM(COUNT(A)) OVER (ORDER BY CODE ) / (SELECT COUNT(A) FROM AA) *100 ),2) AS 'CUMPERCENT'
    from AA, BB
    where AA.A= BB.code
    group by AA.A
)
    select *
from totalcount
    UNION ALL 
SELECT '0' CODE, 'TOTAL' SEX, SUM(TOTAL), SUM(PERCENT), '0' CUMULATIVE, '0.00' CUMPERCENT
    from totalcount
    ORDER BY CODE;

The output was this

CODE | SEX    | TOTAL  | PERCENT | CUMULATIVE | CUMPERCENT
0      TOTAL    5        100.00    0            0.00
1      MALE     3        60.00     3            60.00
2      FEMALE   2        40.00     5            100.00

I used UNION since I want the total of these items. In this case, I inserted 0 in TOTAL of SEX column to put it in the first row. Is there another way aside from using UNION and inserting code 0?

As you can see from my table BB, there are only code 1 and 2 for male and female, respectively. I want to show that the value not in this table should be in NOT VALID.

CODE | SEX       | TOTAL  | PERCENT | CUMULATIVE | CUMPERCENT
0      TOTAL       6        100.00    0            0.00
1      MALE        3        50.00     3            50.00
2      FEMALE      2        33.33     5            83.33
3      NOT VALID   1        16.67     6            100.00

Can you help me with this? Thank you.

user_1863229 · Answer

This is a very tricky reporting question. One approach uses GROUP BY ROLLUP to generate the total row, along with judicious use of COALESCE to fill in the missing values per your requirements.

SELECT
    COALESCE(t1.A, 0) AS CODE,
    CASE WHEN t1.A IS NOT NULL THEN COALESCE(t2.DESCRIPTION, 'NOT VALID') ELSE 'TOTAL' END AS SEX,
    t1.TOTAL,
    ROUND(100.0 * t1.TOTAL / SUM(CASE WHEN t1.A IS NOT NULL THEN t1.TOTAL ELSE 0 END) OVER (), 2) AS PERCENT,
        SUM(CASE WHEN t1.A IS NOT NULL THEN t1.TOTAL ELSE 0 END) OVER (ORDER BY t1.A) AS CUMULATIVE,
    ROUND(100.0 * SUM(CASE WHEN t1.A IS NOT NULL THEN t1.TOTAL ELSE 0 END) OVER (ORDER BY t1.A) /
                  SUM(CASE WHEN t1.A IS NOT NULL THEN t1.TOTAL ELSE 0 END) OVER (), 2) AS CUMPERCENT
FROM
(
    SELECT
        A,
        COUNT(*) AS TOTAL
    FROM AA
    GROUP BY A WITH ROLLUP
) t1
LEFT JOIN BB t2
    ON t2.CODE = t1.A
ORDER BY
    CODE;

How will I fix this table using MySQL?

1 Answers

Demo