SQLite Forum

Help with a query
Login
Hey folks, appreciate your efforts, they have pointed me in the right direction and have, in the light of a new day, found my solution to my own question hidden in the bowels of my own mind.

if this can be done any better, please feel free to comment, apart from an obvious changing of importKwh to the appropriate type without the need to do a cast.

SELECT pollDate, 
       sum(CAST(importKwh AS REAL) /100) as kwh,
       sum(CAST(importKwh AS REAL) /100) *  (SELECT importKwhRate  FROM Readings WHERE periodType = 2 LIMIT  1) as peakCost, 
        (SELECT importKwhRate  FROM Readings WHERE periodType = 2 LIMIT  1) as peakRate,         
       sum(CAST(importKwh AS REAL) /100) *  (SELECT importKwhRate FROM Readings WHERE periodType = 1 LIMIT  1) as offPeakcost,
        (SELECT importKwhRate  FROM Readings WHERE periodType = 1 LIMIT  1) as offPeakRate,                
       sum(CAST(importKwh AS REAL) /100) *  (SELECT importKwhRate  FROM Readings WHERE periodType = 2 LIMIT  1) - sum(CAST(importKwh AS REAL) /100) *  
        (SELECT importKwhRate  FROM Readings WHERE periodType = 1 LIMIT  1) as Diff 
FROM Readings 

WHERE (SELECT interval FROM Readings WHERE interval >= 14 and interval <= 22)  AND pollDate >= '2014//12/29' AND pollDate <= '2015/03/31'  GROUP BY polldate;


RESULT 

pollDate     kwh    peakCost	peakRate   offPeakcost	offPeakRate	Diff
----------------------------------------------------------------------------------------------
2014/12/29   0.659  2.15493    0.327	   1.17302	0.178		0.98191
2014/12/30   1.023  3.34521    0.327	   1.82094	0.178		1.52427
2015/01/01   0.922  3.01494    0.327	   1.64116	0.178		1.37378
2015/01/02   0.283  0.92541    0.327	   0.50374	0.178		0.42167

Cheers