The title is rewritten as: Dynamizing SQL queries

Question

I have the following SQL query and was wondering if I could make the SUM() part of it dynamic so that I don't need to manually enter the 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 item ids and counts of different types of Anomalies based on category_id and status ('En cours' or 'Resolu').

The problem with this query is that if we add another category, I have to manually edit this query, which is not ideal.

Answer 1

The dynamic SQL below is building conditions and sums from a reference table with categories.

project_id	total	Error in progress	Resolved errors	Defects in progress	Resolved defects	Improvements in Progress	Resolved improvements
0	5	1	0	1	1	1	1