CODEWITHSUNDEEP
mysqlsqlgroup-bypivotdynamic-sql
ayoubelmoudni
ayoubelmoudni

Reputation: 85

Making SQL query dynamic

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.

Upvotes: 1

Views: 64

Answers (1)

LukStorms
LukStorms

Reputation: 29647

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

Upvotes: 1

Related Questions