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;
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.
Advertisement
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;
Maybe SUMs must be wrapped with COALESCE() for to replace NULLs to zeros.