SQLTeam.com | Weblogs | Forums

Need to modify query

My SQL coding abilities is nil and I don’t know if this is enough info to solicit an answer, but here goes…

I am trying to create a query based on the following that will output the information in a tabular format by date.

SUM(CASE WHEN loc_n=3 THEN curr_r END) AS "HQ",
SUM(CASE WHEN loc_n=5 AND bus < 8000 THEN curr_r END) AS "PDU2",
SUM(CASE WHEN loc_n=6 THEN curr_r END) AS "PDU3",
SUM(CASE WHEN loc_n=3 OR loc_n=5 AND bus < 8000 OR loc_n=6 THEN curr_r END) AS "TOTAL"
WHERE tday >= TO_DATE('2021-08-24', 'YYYY-MM-DD') AND tday <= TO_DATE('2021-08-24', 'YYYY-MM-DD')
AND loc_n IN (3,5,6)
AND bus < 80000

In other words, assume

  1. Date range of 11-01-2021 through 11-30-2021
  2. Left hand column would appear showing the day of the month
  3. Location columns (HQ, PDU2, PDU3) would appear with their daily totals and the total column would sum the location totals
  4. The monthly totals would appear at the bottom of the table

Thanks in advance for any suggestions.

You need to add a GROUP BY to your query - and if this was SQL Server you could use GROUPING SETS to get additional group totals. But, this looks like MySQL or Oracle and I am not sure what the equivalent syntax is for those systems.

You can also look at GROUP BY WITH ROLLUP - or GROUP BY WITH CUBE.