SQL Subquery with aggregate function – seeking alternative

My table (Table1)

+----+------------+---------+------------+
| id |    Date    |  Houses | kW_measure |
+----+------------+---------+------------+
| 01 | 01/01/2020 | House_A |    2020    |
| 02 | 01/01/2020 | House_B |    2100    |
| 03 | 01/01/2020 | House_C |    2200    |
| 04 | 01/02/2020 | House_A |    2040    |
| 05 | 01/02/2020 | House_B |    2125    |
| 06 | 01/02/2020 | House_C |    2210    |
| 07 | 01/03/2020 | House_A |    2050    |
| 08 | 01/03/2020 | House_B |    2150    |
| 09 | 01/03/2020 | House_C |    2220    |
+----+------------+---------+------------+

I did a sort of Pivot and it’s fine: http://sqlfiddle.com/#!18/45b9d/1/0

+------------+----------+----------+----------+
|    Date    |  House_A |  House_B |  House_C |
+------------+----------+----------+----------+
| 01/01/2020 |   2020   |   2100   |   2200   |
| 01/02/2020 |   2040   |   2125   |   2210   |
| 01/03/2020 |   2050   |   2150   |   2220   |
+------------+----------+----------+----------+

Now I need to calculate the difference between measures, to get something like this:

+------------+----------+-------+
|    Date    |  House_A | Daily |
+------------+----------+-------+
| 01/01/2020 |   2020   |   0   |
| 01/02/2020 |   2040   |   20  |
| 01/03/2020 |   2050   |   10  |
+------------+----------+-------+

But if I don’t use an aggregate, the result brings me null cells – http://sqlfiddle.com/#!18/45b9d/5/0

SELECT (SELECT DISTINCT a.Date)
, SUM(CASE WHEN a.Houses = 'House_A' THEN a.kW_measure END) as 'House_A'
, a.Kw_Measure - (SELECT MIN(b.kW_Measure) FROM Table1 b WHERE b.Houses = 'House_A'
     AND b.Date < a.Date)

FROM Table1 a

GROUP BY Date, kW_Measure
ORDER BY Date ASC

And if I do use it, I get the message:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Any help would be great.

Answer

You can use window functions. Starting from your existing conditional aggregation query, this computes the consumption difference between the current and previous date for each house:

select
    date,
    house_a,
    coalesce(house_a - lag(house_a) over(order by date), 0) daily_a,
    house_b,
    coalesce(house_b - lag(house_b) over(order by date), 0) daily_b,
    house_c,
    coalesce(house_c - lag(house_c) over(order by date), 0)daily_c
from (
    select 
        date, 
        sum(case when houses = 'House_A' then kw_measure end) as house_a,
        sum(case when houses = 'House_B' then kw_measure end) as house_b,
        sum(case when houses = 'House_C' then kw_measure end) as house_c
    from table1
    group by date
) t
order by date

The subquery is not technically necessary, but it avoids repeating the conditional sum()s twice.