Problem calculating in SELECT based on 2 subqueries on same table-CodePudding

I have a table containing meter readings logged on monthly basis including information on sunshine hours and heating degree days. Using this table I try to calculate efficiency in our solarpanels as kwh produced per sunhour - on yearly basis. Goal is to identify possible decrease in production over time (age of solarpanels).

I can use meter readings to calculate yearly consumption (by subtracting last year end reading form this year end reading) - and I can sum up sunhours (monthly reading per year) - but my (selflearned) skills does enable me to get the combined calculation to work.

Result should be result listing 'Season, efficiency' to use in a graphing tool.

First attempt:

SELECT
    season,
    solarpanel - LAG(solarpanel, 1, 17449) OVER( ORDER BY season) AS kwh
FROM 
    kv28c_meterreadings_readings 
WHERE 
    reading = 12

UNION 

SELECT 
    season, SUM(sun_hrs) AS hours
FROM 
    kv28c_meterreadings_readings
GROUP BY 
    season
ORDER BY 
    season

This returns a list:

season     kwh
-----------------
2018/19    1891.0
2018/19    1925.0
2019/20    1802.2
2019/20    1770.0

Now I need to divide the second row by the first row.

Latest attempt:

SELECT
    k.season,
    (k.solarpanel - LAG(k.solarpanel, 1, 17449) OVER( ORDER BY k.season)) / SUM(h.sun_hrs) AS k.efficiency
FROM 
    kv28c_meterreadings_readings k 
WHERE 
    k.reading = 12
JOIN 
    kv28c_meterreadings_readings h ON k.season = h.season 
ORDER BY 
    k.season

which fails on syntax at WHERE.

What to do?

CodePudding user response：

Without seeing your table structure, it's hard to know what the best answer would be but I've come up with two possibilities.

No joins - Everything is coming from the same table. Pull it all at the same time.

 SELECT season
      , solarpanel - LAG(solarpanel, 1, 17449) OVER( ORDER BY season) as kwh
      , SUM(sun_hrs) as hours
      , (solarpanel - LAG(solarpanel, 1, 17449) OVER( ORDER BY season))/(SUM(sun_hrs)) as efficiency
   FROM kv28c_meterreadings_readings 
  WHERE reading = 12 
  ORDER BY season

CTE - a cte to separate kwh from hours.

WITH power AS
     ( SELECT season
            , solarpanel - LAG(solarpanel, 1, 17449) OVER( ORDER BY season) as kwh
         FROM kv28c_meterreadings_readings 
        WHERE reading = 12 
     )
     , sun AS
     ( SELECT season
            , SUM(sun_hrs) AS hours
         FROM kv28c_meterreadings_readings    
     )
 SELECT power.season
      , power.kwh / sun.hours AS efficiency
   FROM power
   JOIN sun ON power.season = sun.season
  ORDER BY power.season

CodePudding user response：

to get your numbers, you can use two cte and join the results

WITH CTE_Reading as(
SELECT
    season,
    solarpanel - LAG(solarpanel, 1, 17449) OVER( ORDER BY season) AS kwh
FROM 
    kv28c_meterreadings_readings 
WHERE 
    reading = 12),
CTE_group as(
SELECT 
    season, SUM(sun_hrs) AS hours
FROM 
    kv28c_meterreadings_readings
GROUP BY 
    season)
SELECT c1.season, kwh/hours
FROM CTE_Reading c1 JOIN CTE_group c2 ON c1.season = c2.season
ORDER BY 
    c1.season