I have two tables:
Meter (condensed)
id Meter_Id Date Time Consumption
1 1 2016-01-01 0.5 0.0769
2 1 2016-01-01 1.0 0.0649
3 1 2016-01-01 1.5 0.0379
4 1 2016-01-01 2.0 0.0439
5 1 2016-01-01 2.5 0.045
6 1 2016-01-01 3.0 0.034
7 1 2016-01-01 3.5 0.0419
8 1 2016-01-01 4.0 0.0399
9 1 2016-01-01 4.5 0.0329
10 1 2016-01-01 5.0 0.045
11 1 2016-01-01 5.5 0.035
12 1 2016-01-01 6.0 0.035
13 1 2016-01-01 6.5 0.0439
14 1 2016-01-01 7.0 0.0309
15 1 2016-01-01 7.5 0.039
16 1 2016-01-01 8.0 0.041
17 1 2016-01-01 8.5 0.032
18 1 2016-01-01 9.0 0.0399
19 1 2016-01-01 9.5 0.039
20 1 2016-01-01 10.0 0.032
21 1 2016-01-01 10.5 0.039
22 1 2016-01-01 11.0 0.0399
23 1 2016-01-01 11.5 0.032
24 1 2016-01-01 12.0 0.041
25 1 2016-01-01 12.5 0.039
26 1 2016-01-01 13.0 0.032
27 1 2016-01-01 13.5 0.676
28 1 2016-01-01 14.0 0.0379
29 1 2016-01-01 14.5 0.0329
30 1 2016-01-01 15.0 0.0419
31 1 2016-01-01 15.5 0.0379
32 1 2016-01-01 16.0 0.032
33 1 2016-01-01 16.5 0.046
34 1 2016-01-01 17.0 0.035
35 1 2016-01-01 17.5 0.037
36 1 2016-01-01 18.0 0.0469
37 1 2016-01-01 18.5 0.032
38 1 2016-01-01 19.0 0.0399
39 1 2016-01-01 19.5 0.041
40 1 2016-01-01 20.0 0.0309
41 1 2016-01-01 20.5 0.043
42 1 2016-01-01 21.0 0.037
43 1 2016-01-01 21.5 0.0329
44 1 2016-01-01 22.0 0.0439
45 1 2016-01-01 22.5 0.0329
46 1 2016-01-01 23.0 0.034
47 1 2016-01-01 23.5 0.043
48 1 2016-01-01 24.0 0.032
49 1 2016-01-02 0.5 0.032
50 1 2016-01-02 1.0 0.0439
51 1 2016-01-02 1.5 0.0309
52 1 2016-01-02 2.0 0.034
53 1 2016-01-02 2.5 0.0419
54 1 2016-01-02 3.0 0.0309
55 1 2016-01-02 3.5 0.035
56 1 2016-01-02 4.0 0.041
57 1 2016-01-02 4.5 0.0299
58 1 2016-01-02 5.0 0.3049
59 1 2016-01-02 5.5 0.446
60 1 2016-01-02 6.0 0.0299
61 1 2016-01-02 6.5 0.0299
62 1 2016-01-02 7.0 0.0419
63 1 2016-01-02 7.5 0.0329
64 1 2016-01-02 8.0 0.0299
65 1 2016-01-02 8.5 0.037
66 1 2016-01-02 9.0 0.037
67 1 2016-01-02 9.5 0.0309
68 1 2016-01-02 10.0 0.0299
69 1 2016-01-02 10.5 0.0399
70 1 2016-01-02 11.0 0.035
71 1 2016-01-02 11.5 0.0299
72 1 2016-01-02 12.0 0.037
73 1 2016-01-02 12.5 0.039
74 1 2016-01-02 13.0 0.0309
75 1 2016-01-02 13.5 0.0309
76 1 2016-01-02 14.0 0.0419
77 1 2016-01-02 14.5 0.0359
78 1 2016-01-02 15.0 0.0309
79 1 2016-01-02 15.5 0.0399
80 1 2016-01-02 16.0 0.037
81 1 2016-01-02 16.5 0.032
82 1 2016-01-02 17.0 0.0379
83 1 2016-01-02 17.5 0.041
84 1 2016-01-02 18.0 0.032
85 1 2016-01-02 18.5 0.037
86 1 2016-01-02 19.0 0.041
87 1 2016-01-02 19.5 0.0309
88 1 2016-01-02 20.0 0.039
89 1 2016-01-02 20.5 0.048
90 1 2016-01-02 21.0 0.074
91 1 2016-01-02 21.5 0.7059
92 1 2016-01-02 22.0 0.046
93 1 2016-01-02 22.5 0.0329
94 1 2016-01-02 23.0 0.035
95 1 2016-01-02 23.5 0.0399
96 1 2016-01-02 24.0 0.0329
97 1 2016-01-03 0.5 0.0299
...
Tariff
ID CHARGE
1 13.38
I'm trying to create a query on the consumption with some conditions:
CHARGE. Call this Peak_Charge.Peak_Charge values for each day into a Total_Peak_Charge value.e.g. In the above tables, the MAX of day 2016-01-01 in peak hours is 0.0469 and day 2016-01-02 is 0.048. With these, I need to return a Total_Peak_Charge value that is equal to (0.0469 * 13.38) + (0.048 * 13.38) = 1.269762.
In your question you don't clarify how Meter is related to Tariff.
If the columns Meter.meter_id and Tariff.id are related then join the tables, aggregate and then use window function sum():
select distinct
t.charge * sum(max(m.Consumption)) over () Total_Peak_Charge
from Meter m inner join Tariff t
on t.id = m.meter_id
where m.Time between 15.0 and 20.5
group by m.Date
If the 2 tables are unrelated and Tariff contains only 1 row, then:
select distinct
(select charge from Tariff) * sum(max(Consumption)) over () Total_Peak_Charge
from Meter
where Time between 15.0 and 20.5
group by Date;
See the demo.
Results:
| Total_Peak_Charge |
| ----------------- |
| 1.269762 |