MySQL View with Conditional Sum

Question

I’m trying to create a view that SUMS a table column based on two other columns (id and class), but I’m struggling with the inner query, or at least I think thats the problem.

The VIEW is not recognizing the ‘id’ column and thus is summing the entire column for each id.

I’ve left my attempts out of the fiddle for simplicity and easier understanding.

Here is my sample data...

CREATE TABLE Table1 (id INT,class varchar(10),amount varchar(50));

INSERT INTO Table1 (id,class,amount) 
VALUES
(1,'A',.25),
(1,'B',.25),
(1,'B',.25),
(1,'C',.25),
(1,'C',.25),
(1,'C',.25),
(2,'A',.25),
(2,'B',.25),
(2,'B',.25),
(2,'C',.25),
(2,'C',.25),
(2,'C',.25);

...and fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e6aa3d66e791936779ea3a4c5b0d8580

CREATE TABLE Table1 (id INT,class varchar(10),amount varchar(50));

CREATE OR REPLACE VIEW View1 AS

select id,
    concat_ws(' / ',
        concat_ws('','A:',(select  coalesce(SUM(amount),0)
                            from Table1
                            where class = 'A')),
        concat_ws('','B:',(select  coalesce(SUM(amount),0)
                            from Table1
                        where class = 'B')),
        concat_ws('','C:',(select  coalesce(SUM(amount),0)
                            from Table1
                            where class = 'C'))

    ) as totals
from Table1
group by id

The expected outcome for each id should be: A: 0.25 / B: 0.50 / C: 0.75

Take `GROUP BY` out of the main query. The conditionals are doing the grouping. — Barmar, Jul 19 '21 at 14:52

Barmar · Accepted Answer · 2021-07-19T15:10:13.670

1

Instead of grouping with conditionals, just do grouped sums in a subquery, then concatenate them.

CREATE OR REPLACE VIEW View1 AS

SELECT id, GROUP_CONCAT(class, ': ', ROUND(total, 2) ORDER BY class SEPARATOR '/') AS totals
FROM (
    SELECT id, class, SUM(amount) AS total
    FROM Table1
    GROUP BY id, class
) AS x
GROUP BY id

Fiddle

edited Jul 19 '21 at 15:10

answered Jul 19 '21 at 14:59

Barmar

741,623
53
500
612

Thanks, that works, but it’s not summing correctly. I changed the values and summing .10+.10+.10 is returning .30000004. Why? See fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9d7d0c17aa4030042609289ee3d927c4 – aimee Jul 19 '21 at 15:06
Use `ROUND(total, 2)` to round to 2 decimal places – Barmar Jul 19 '21 at 15:06
See https://stackoverflow.com/questions/588004/is-floating-point-math-broken – Barmar Jul 19 '21 at 15:06
Why are you storing `amount` as a `VARCHAR` instead of `DECIMAL`? – Barmar Jul 19 '21 at 15:07
there are strings in the column. I understand it’s not best practice and will rebuild in the future. Would I round the sum(amount)? – aimee Jul 19 '21 at 15:09
I've updated the answer to show where to round. – Barmar Jul 19 '21 at 15:10

score 0 · Answer 2 · answered Jul 19 '21 at 14:52

0

SELECT id,class, SUM(amount) FROM Table1 GROUP BY id,class;

answered Jul 19 '21 at 14:52

MySQL View with Conditional Sum

2 Answers2