Making SQL query dynamic

Question

i have the following SQL query and i was wondering if i can make the SUM() part of it dynamic so that i don't have to put category_id (2 and 3)

SELECT 
a.project_id,
COUNT(a.id) AS Total,
SUM(CASE WHEN a.category_id = 2 AND a.`status` < 80 THEN 1 ELSE 0 END) AS 'Bugs En cours',
SUM(CASE WHEN a.category_id = 2 AND a.`status` >= 80 THEN 1 ELSE 0 END) AS 'Bugs Resolu',
SUM(CASE WHEN a.category_id = 3 AND a.`status` < 80 THEN 1 ELSE 0 END) AS 'Ameliorations En cours',
SUM(CASE WHEN a.category_id = 3 AND a.`status` >= 80 THEN 1 ELSE 0 END) AS 'Ameliorations Resolu'
FROM bugs a 
GROUP BY a.project_id
HAVING COUNT(a.id) > 0

The goal is to list the project id and the count of different Anomalies depending on the category_id and on the status ('En cours' OR 'Resolu')

The issue in this query is that if we added another category, i will have to manually edit this query which is not ideal.

Why not just put those category/status mappings in a table instead of splattering enum strings all over your query text? — Aaron Bertrand
– Aaron Bertrand, Commented Jan 22, 2022 at 15:07
Unfortunately, i don't have a saying of how the tables are structured. — ayoubelmoudni
– ayoubelmoudni, Commented Jan 22, 2022 at 15:12
Sure, but this is feedback you could give to the people who do. — Aaron Bertrand
– Aaron Bertrand, Commented Jan 22, 2022 at 15:14
It would not be difficult to create a dynamic query which based on the category_ids found in bugs so if a new category appeared that would not be a problem EXCEPT the query would not know what label should be attached to it and if the query was expected to show where new category came into being but didn't appear in bugs it wouldn't be able to do that either. SO it seems you are stuck with manual changes unless you can persuade the powers that be of your case to add a table.. — P.Salmon
– P.Salmon, Commented Jan 22, 2022 at 15:33

LukStorms · Accepted Answer · 2022-01-22 16:31:32Z

The dynamic sql below is building the conditional sums from on a reference table with the categories.

SET @sums := (
SELECT GROUP_CONCAT(
CONCAT(
', SUM(CASE WHEN category_id = ', category_id, 
' AND `status` <  80 THEN 1 ELSE 0 END)', 
' AS `', category_name, ' En cours`', CHAR(10),
', SUM(CASE WHEN category_id = ', category_id, 
' AND `status` >= 80 THEN 1 ELSE 0 END)',
' AS `', category_name, ' Resolu`', CHAR(10)
) ORDER BY category_id SEPARATOR '') as sums
FROM categories
);

SET @dynsql := CONCAT('SELECT', CHAR(10), '  bug.project_id', CHAR(10),
', COUNT(bug.id) AS Total', CHAR(10),
@sums, 
'FROM bugs bug', CHAR(10),
'GROUP BY bug.project_id', CHAR(10),
'HAVING COUNT(bug.id) > 0;');

-- SELECT @dynsql AS dynsql;
PREPARE dyn_stmt FROM @dynsql;
EXECUTE dyn_stmt;
DEALLOCATE PREPARE dyn_stmt;

project_id	Total	Conneries En cours	Conneries Resolu	Bugs En cours	Bugs Resolu	Ameliorations En cours	Ameliorations Resolu
0	5	1	0	1	1	1	1

Demo on db<>fiddle here

Collectives™ on Stack Overflow

Making SQL query dynamic

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