D_Thi D_Thi - 4 months ago 6
SQL Question

How to query the same column three times with three different conditions?

In a SQL-Statement I need to query the same column three times with three different conditions.
I need the SUM of CC.HR1*WLL17.Hours displayed for all Locations but with three different conditions. I tried a subquery in the SELECT statement but then I only get the total sum and not the sum grouped by location.

SELECT WLL17.Location, (SELECT SUM(CC.HR1*WLL17.Hours) FROM WLL17 INNER JOIN CC ON WLL17.CC=CC.CC WHERE WLL17.EE = "Post Gate 60 (PV)") AS Plant
FROM WLL17 INNER JOIN CC ON WLL17.CC = CC.CC
WHERE WLL17.CC<>"Outsourced Costs"
GROUP BY WLL17.Location;

Answer

Try to follow this strategy.

SELECT WLL17.Location, 
    SUM(case when WLL17.EE = "Post Gate 60 (PV)" then CC.HR1*WLL17.Hours else null end) AS Plant,         
    SUM(case when WLL17.CC<>"Outsourced Costs"  then CC.HR1*WLL17.Hours else null end) AS Location_Name1,
    SUM(case when [Write condition here] then CC.HR1*WLL17.Hours else null end) AS Location_Name2
FROM WLL17 INNER JOIN CC ON WLL17.CC = CC.CC
GROUP BY WLL17.Location;