MCSN MCSN - 3 months ago 7
SQL Question

SQL query getting multiple where-claused aliases

Hoping you can help with this issue.

I have an energymanagement software running on a system. The data logged is the total value, logged in the column Value. This is done every hour. Along is some other data, here amongst a boolean called

Active
and an integer called
Day
.

What I'm going for, is one query that gets me the a list of sorted days, the total powerusage of the day, and the peak-powerusage of the day.

The peak-power usage is counted by using Max/Min of the value where
Active
is present. Somedays, however, the
Active
bit isn't set, and the result of this query alone would yield NULL.

This is my query:

SELECT
A.Day, A.Forbrug, B.Peak
FROM
(SELECT
Day, Max(Value) - Min(Value) AS Forbrug
FROM
EL_HT1_K
WHERE
MONTH = 8 AND YEAR = 2016
GROUP By Day) A,
(SELECT
Day, Max(Value) - Min(Value) AS Peak
FROM
EL_HT1_K
WHERE
Month = 8 AND Year = 2016 AND Active = 1
GROUP BY Day) B
WHERE
A.Day = B.Day


Which only returns the result where query B (Peak-usage) would yield results.
What I want, is that the rest of the results from inner query A, still is shown, even though query B yields 0/null for that day.

Is this possible, and how?

FYI. The reason I need this to be in one query, is that the scada system has some difficulties handling multiple queries.

Answer

I think you just want conditional aggregation. Based on your description, this seems to be the query you want:

SELECT Day, SUM(Value) as total,
       MAX(CASE WHEN Active = 1 THEN Value END) as Peak,
FROM EL_HT1_K
WHERE Month = 8 AND  Year = 2016 
GROUP BY Day;
Comments