SQL: Add multiple where clauses in a single query

I want to run an SQL query from Node.js. I am currently showing the total number of projects that have a specific status in each of the 4 quarters. What I want to do now is show the same result but while adding one more condition i.e, Fiscal Year.

Here’s my code for the 4 quarters:

SELECT 
SUM(CurrentStatus = 'On Hold') onHold_Q,
SUM(CurrentStatus = 'In Progress') inProgress_Q,
SUM(CurrentStatus = 'Not Started') notStarted_Q,
SUM(CurrentStatus = 'Completed') completed_Q,
SUM(CurrentStatus = 'Routine Activity') routineActivity_Q,
SUM(CurrentStatus = 'Done But Not Published') doneButNotPublished_Q
FROM office.officedata
WHERE Quarter in ('Q1', 'Q2', 'Q3', 'Q4')    //here I want to add one more condition FiscalYear in ('2016-17')
GROUP BY Quarter
ORDER BY Quarter;

​x
 
SELECT SUM(CurrentStatus = 'On Hold') onHold_Q,SUM(CurrentStatus = 'In Progress') inProgress_Q,SUM(CurrentStatus = 'Not Started') notStarted_Q,SUM(CurrentStatus = 'Completed') completed_Q,SUM(CurrentStatus = 'Routine Activity') routineActivity_Q,SUM(CurrentStatus = 'Done But Not Published') doneButNotPublished_QFROM office.officedataWHERE Quarter in ('Q1', 'Q2', 'Q3', 'Q4')    //here I want to add one more condition FiscalYear in ('2016-17')GROUP BY QuarterORDER BY Quarter;​

This prints output in 4 different rows which is exactly what I want. But, when I add one more condition then it does execute but only one row gets printed since the projects in the rest of the 3 quarters for that year do not exist. I want to somehow print 4 different rows for quarters for every Fiscal Year. The query should print 0 in the row if no projects exist for that quarter in a year. How can I do that? My SQL is not that strong, it would be great if someone can help me out.

Answer

Can you tell me programmatically how to do that?

SELECT years.FiscalYear, quarters.Quarter,
SUM(CurrentStatus = 'On Hold') onHold_Q,
SUM(CurrentStatus = 'In Progress') inProgress_Q,
SUM(CurrentStatus = 'Not Started') notStarted_Q,
SUM(CurrentStatus = 'Completed') completed_Q,
SUM(CurrentStatus = 'Routine Activity') routineActivity_Q,
SUM(CurrentStatus = 'Done But Not Published') doneButNotPublished_Q
FROM (SELECT 2016 FiscalYear UNION SELECT 2017) years
CROSS JOIN (SELECT 'Q1' Quarter UNION SELECT 'Q2' UNION SELECT 'Q3' UNION SELECT 'Q4') quarters
LEFT JOIN office.officedata ON office.FiscalYear = years.FiscalYear AND office.Quarter = quarters.Quarter 
GROUP BY years.FiscalYear, quarters.Quarter
ORDER BY years.FiscalYear, quarters.Quarter;

 
SELECT years.FiscalYear, quarters.Quarter,SUM(CurrentStatus = 'On Hold') onHold_Q,SUM(CurrentStatus = 'In Progress') inProgress_Q,SUM(CurrentStatus = 'Not Started') notStarted_Q,SUM(CurrentStatus = 'Completed') completed_Q,SUM(CurrentStatus = 'Routine Activity') routineActivity_Q,SUM(CurrentStatus = 'Done But Not Published') doneButNotPublished_QFROM (SELECT 2016 FiscalYear UNION SELECT 2017) yearsCROSS JOIN (SELECT 'Q1' Quarter UNION SELECT 'Q2' UNION SELECT 'Q3' UNION SELECT 'Q4') quartersLEFT JOIN office.officedata ON office.FiscalYear = years.FiscalYear AND office.Quarter = quarters.Quarter GROUP BY years.FiscalYear, quarters.QuarterORDER BY years.FiscalYear, quarters.Quarter;​

Maybe SUMs must be wrapped with COALESCE() for to replace NULLs to zeros.

Advertisement

Answer