Creating a query containing a SUM() of MAX() values with conditions

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:

• It must only look at consumption values between “peak hours” (15, 15.5, 16, …, 19.5, 20, 20.5).
• For each day, find the highest (MAX) value and multiply by the tariff CHARGE. Call this Peak_Charge.
• SUM the 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.

Answer

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          |