case statement in complex SQL
Hi Expert,
I need this output through creating view , can you please advice here
I'm using aws redshift so I can't use CUBE or ROLLUP, I've attached file to better viewing.
Thank you,Meshblock-Group-Tariff-Combinations-Coun
I need this output through creating view , can you please advice here
Customer Group Tariff Combinations Count AvgAnnualSpend
A 11 1902 2,198.00
11 and 31 324 1,866.00
11 and 31 and SA or SSA 94 1,258.00
B
etc…I'm using aws redshift so I can't use CUBE or ROLLUP, I've attached file to better viewing.
Thank you,Meshblock-Group-Tariff-Combinations-Coun
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I thought this would be in SQL topic. anyway my apology.
Vitor Montalvão can you suggest how to display that result through sql ? Thank you
Vitor Montalvão can you suggest how to display that result through sql ? Thank you
ASKER
PortletPaul I'll try this and get back to you..Thank you
ASKER
Hi Porlet, I change your query and this is what I'm getting.
but out put is not what we looking for, just for yours more understanding , I've past the data file where data something like this, it is not align but you can understand :)
select
meshblock_group
, case when tariff_code = 11 and tariff_code=31 and tariff_code in ('SA','SSA') then '11 and 31 and SA or SSA'
when tariff_code = 11 and tariff_code=31 then '11 and 31'
when tariff_code = 11 then '11'
end
, count(*)
, AVG(kwh_charge)
from public.habidapt_2014_10_10
where tariff_code = 11
group by
meshblock_group
, case when tariff_code = 11 and tariff_code=31 and tariff_code in ('SA','SSA') then '11 and 31 and SA or SSA'
when tariff_code = 11 and tariff_code=31 then '11 and 31'
when tariff_code = 11 then '11'
end
order by meshblock_group;
11 83169 402.90
A - Exclusive Environs 11 3426 569.87
B - Knowledgable Success 11 21914 518.35
C - Independence and Careers 11 15030 378.64
D - Affluent Acreage 11 17532 472.06
E - Distanced Existence 11 36926 530.96
F - New Homes and Hopes 11 178937 464.98
G - Middle Australia 11 73754 426.26
H - International Infusion 11 207 412.83
I - Books and Boots 11 232 350.75
J - Provincial Living 11 215615 374.35
K - Traditionally Grey 11 38236 317.30
L - Regional Endeavours 11 98207 388.74
M - Remotely Blue 11 54852 372.75
U - Unknown 11 7928 400.12but out put is not what we looking for, just for yours more understanding , I've past the data file where data something like this, it is not align but you can understand :)
sno postcode pv_capacity_kw pv_panel_kw tariff_code end_month meshblock_group kwh_consumption kwh_charge
68476 4815 NULL NULL 33 2013-12-01 00:00:00 J - Provincial Living 343 68.11
94627 4812 NULL NULL 33 2014-08-01 00:00:00 J - Provincial Living 468 94.17
94627 4812 NULL NULL 11 2014-08-01 00:00:00 J - Provincial Living 826 307.93
22448 4818 2.00 2.00 11 2014-07-01 00:00:00 F - New Homes and Hopes 555 219.84
18743 4812 NULL NULL 33 2014-01-01 00:00:00 J - Provincial Living 230 45.67
59747 4814 NULL NULL 33 2013-12-01 00:00:00 L - Regional Endeavours 686 136.22
58172 4815 4.60 5.00 SS 2014-06-01 00:00:00 F - New Homes and Hopes 980 -431.20
94639 4870 NULL NULL 11 2014-07-01 00:00:00 J - Provincial Living 1037 357.11
44489 4812 NULL NULL 33 2014-05-01 00:00:00 J - Provincial Living 456 90.55
158514 4870 NULL NULL 11 2013-07-01 00:00:00 M - Remotely Blue 728 225.02
94639 4870 NULL NULL 11 2014-04-01 00:00:00 J - Provincial Living 1451 478.57
94581 4868 NULL NULL 11 2013-09-01 00:00:00 G - Middle Australia 612 223.19
158421 4850 NULL NULL 11 2014-03-01 00:00:00 0 53.03
33377 4817 4.80 4.80 11 2014-02-01 00:00:00 F - New Homes and Hopes 248 84.53
72299 4850 4.00 4.00 11 2014-05-01 00:00:00 M - Remotely Blue 1429 470.44
98932 4817 NULL NULL 11 2014-03-01 00:00:00 G - Middle Australia 3560 1095.36
32166 4879 NULL NULL 11 2013-08-01 00:00:00 K - Traditionally Grey 877 286.10
179965 4814 NULL NULL 11 2013-09-01 00:00:00 K - Traditionally Grey 1369 434.78
179965 4814 NULL NULL 11 2013-12-01 00:00:00 K - Traditionally Grey 797 282.40
81755 4870 NULL NULL 11 2013-08-01 00:00:00 F - New Homes and Hopes 1400 422.12
137925 4817 4.60 3.50 11 2014-07-01 00:00:00 F - New Homes and Hopes 1284 436.28
175103 4817 NULL NULL 11 2014-05-01 00:00:00 F - New Homes and Hopes 10 18.41
66457 4870 NULL NULL 33 2014-07-01 00:00:00 J - Provincial Living 529 105.68
130166 4814 NULL NULL 33 2013-09-01 00:00:00 B - Knowledgable Success 1114 212.75
121584 4870 NULL NULL 11 2013-10-01 00:00:00 J - Provincial Living 293 131.45
59084 4817 NULL NULL 11 2014-07-01 00:00:00 F - New Homes and Hopes 1704 554.00
169535 4350 NULL NULL 11 2013-12-01 00:00:00 L - Regional Endeavours 732 266.05
45809 4868 NULL NULL 33 2014-06-01 00:00:00 M - Remotely Blue 334 66.32
135642 4815 3.00 3.00 33 2014-04-01 00:00:00 L - Regional Endeavours 1135 225.38
135642 4815 3.00 3.00 11 2014-01-01 00:00:00 L - Regional Endeavours 966 337.61
7459 4870 NULL NULL 11 2013-07-01 00:00:00 K - Traditionally Grey 709 212.34
138747 4818 NULL NULL 33 2013-07-01 00:00:00 L - Regional Endeavours 364 64.72
138747 4818 NULL NULL 11 2013-07-01 00:00:00 L - Regional Endeavours 1036 305.76
170496 4815 NULL NULL 33 2013-12-01 00:00:00 L - Regional Endeavours 607 120.54
127448 4814 NULL NULL 11 2013-12-01 00:00:00 G - Middle Australia 2955 919.13
61232 4817 NULL NULL 11 2013-10-01 00:00:00 M - Remotely Blue 533 205.33
6297 4870 NULL NULL 11 2014-04-01 00:00:00 B - Knowledgable Success 869 306.33
146396 4878 NULL NULL 11 2013-08-01 00:00:00 1049 329.61
33238 4879 NULL NULL 11 2014-05-01 00:00:00 686 251.98
140477 4350 NULL NULL 33 2013-11-01 00:00:00 E - Distanced Existence 435 86.38
81988 4818 4.60 5.00 33 2013-07-01 00:00:00 E - Distanced Existence 568 110.85
81988 4818 4.60 5.00 SSA 2013-10-01 00:00:00 E - Distanced Existence 813 -65.04
81988 4818 4.60 5.00 33 2013-10-01 00:00:00 E - Distanced Existence 750 160.93
77281 4350 NULL NULL 33 2014-08-01 00:00:00 J - Provincial Living 407 81.94
77281 4350 NULL NULL 33 2014-05-01 00:00:00 J - Provincial Living 288 57.19
79567 4850 NULL NULL 11 2014-02-01 00:00:00 M - Remotely Blue 537 208.16
146684 4868 NULL NULL 33 2014-09-01 00:00:00 F - New Homes and Hopes 851 172.15
51353 4870 NULL NULL 31 2014-07-01 00:00:00 G - Middle Australia 987 132.43
152966 4868 NULL NULL 11 2014-03-01 00:00:00 J - Provincial Living 1117 379.25
162775 4814 NULL NULL 11 2014-06-01 00:00:00 J - Provincial Living 957 333.32
65482 4350 NULL NULL 33 2014-08-01 00:00:00 E - Distanced Existence 563 113.39
123831 4818 NULL NULL 11 2014-07-01 00:00:00 F - New Homes and Hopes 1856 600.69
188398 4814 NULL NULL 11 2014-03-01 00:00:00 J - Provincial Living 1238 412.07
42233 4814 NULL NULL 11 2014-06-01 00:00:00 J - Provincial Living 1415 467.98
42304 4870 NULL NULL 11 2013-11-01 00:00:00 J - Provincial Living 31 10.78
30938 4870 NULL NULL 11 2014-07-01 00:00:00 J - Provincial Living 632 241.03
106739 4814 NULL NULL 11 2013-09-01 00:00:00 G - Middle Australia 1413 459.93
106498 4350 4.60 4.60 11 2013-09-01 00:00:00 F - New Homes and Hopes 1661 519.60
106739 4814 NULL NULL 33 2014-09-01 00:00:00 G - Middle Australia 480 97.19
155164 4870 NULL NULL 11 2014-04-01 00:00:00 J - Provincial Living 490 163.42
120679 4817 NULL NULL 11 2014-04-01 00:00:00 G - Middle Australia 1810 575.83
120679 4817 NULL NULL 31 2014-02-01 00:00:00 G - Middle Australia 516 70.21
12084 4817 4.90 5.00 SS 2013-08-01 00:00:00 G - Middle Australia 147 -64.68
122193 4814 NULL NULL 33 2013-12-01 00:00:00 G - Middle Australia 2209 438.65
33068 4850 NULL NULL 33 2014-08-01 00:00:00 K - Traditionally Grey 265 53.20
52658 4814 NULL NULL 11 2014-03-01 00:00:00 J - Provincial Living 632 234.43
24018 4850 NULL NULL 11 2014-05-01 00:00:00 M - Remotely Blue 0 53.58
20108 4350 NULL NULL 11 2013-12-01 00:00:00 K - Traditionally Grey 418 173.72
30170 4870 NULL NULL 11 2014-01-01 00:00:00 J - Provincial Living 1200 405.32
27572 4817 NULL NULL 11 2014-05-01 00:00:00 F - New Homes and Hopes 1436 473.04
124745 4811 4.60 4.60 33 2014-08-01 00:00:00 F - New Homes and Hopes 866 174.22
183227 4350 NULL NULL 33 2014-02-01 00:00:00 J - Provincial Living 459 103.15
77642 4350 NULL NULL 33 2014-03-01 00:00:00 L - Regional Endeavours 322 63.94
183759 4350 5.00 5.00 11 2014-09-01 00:00:00 L - Regional Endeavours 1698 559.50
50384 4350 NULL NULL 33 2013-11-01 00:00:00 E - Distanced Existence 915 181.70
7401 4350 NULL NULL 33 2014-04-01 00:00:00 L - Regional Endeavours 0 0.00
187480 4814 NULL NULL 33 2014-08-01 00:00:00 F - New Homes and Hopes 1647 330.65
134260 4811 NULL NULL 11 2014-05-01 00:00:00 F - New Homes and Hopes 893 311.18
69398 4817 NULL NULL 33 2014-01-01 00:00:00 7910 1570.70
13541 4811 NULL NULL 33 2013-08-01 00:00:00 F - New Homes and Hopes 542 102.28
154410 4350 NULL NULL 33 2013-09-01 00:00:00 L - Regional Endeavours 770 148.36
157059 4815 NULL NULL 11 2014-01-01 00:00:00 F - New Homes and Hopes 4269 1306.58
120488 4802 1.20 1.20 11 2014-04-01 00:00:00 L - Regional Endeavours 712 256.86
187682 4350 NULL NULL 33 2014-06-01 00:00:00 E - Distanced Existence 735 145.95
72285 4818 NULL NULL 11 2013-07-01 00:00:00 F - New Homes and Hopes 1531 429.64
159602 4870 NULL NULL 11 2014-07-01 00:00:00 K - Traditionally Grey 743 272.70
166978 4350 NULL NULL 11 2014-05-01 00:00:00 E - Distanced Existence 737 266.42
166978 4350 NULL NULL 11 2014-08-01 00:00:00 E - Distanced Existence 823 304.41
163959 4350 NULL NULL 11 2013-09-01 00:00:00 L - Regional Endeavours 863 288.50
94265 4350 NULL NULL 11 2014-01-01 00:00:00 J - Provincial Living 739 268.65
39596 4817 4.60 5.00 11 2014-08-01 00:00:00 F - New Homes and Hopes 820 303.02
39596 4817 4.60 5.00 33 2014-02-01 00:00:00 F - New Homes and Hopes 83 16.48
75719 4869 NULL NULL 31 2014-09-01 00:00:00 F - New Homes and Hopes 356 46.07
87586 4865 NULL NULL 11 2014-03-01 00:00:00 F - New Homes and Hopes 961 335.60
24123 4350 NULL NULL 31 2014-03-01 00:00:00 L - Regional Endeavours 776 105.59
24123 4350 NULL NULL 31 2014-06-01 00:00:00 L - Regional Endeavours 520 70.75
61555 4350 2.00 1.90 33 2013-10-01 00:00:00 M - Remotely Blue 567 112.59
27988 4870 1.60 1.60 SSA 2013-07-01 00:00:00 K - Traditionally Grey 307 -24.56
37895 4814 NULL NULL 11 2013-08-01 00:00:00 F - New Homes and Hopes 1626 499.43
183786 4818 1.00 1.00 SS 2014-05-01 00:00:00 F - New Homes and Hopes 152 -66.88
29308 4350 1.60 1.60 SS 2013-08-01 00:00:00 J - Provincial Living 437 -192.28
159689 4870 NULL NULL 11 2013-10-01 00:00:00 K - Traditionally Grey 1171 396.79
52837 4870 NULL NULL 11 2014-04-01 00:00:00 G - Middle Australia 2841 888.37
68476 4815 NULL NULL 11 2014-09-01 00:00:00 J - Provincial Living 634 254.90
84175 4814 NULL NULL 11 2014-05-01 00:00:00 F - New Homes and Hopes 9 53.47
94627 4812 NULL NULL 33 2014-05-01 00:00:00 J - Provincial Living 321 63.75
94627 4812 NULL NULL 11 2013-11-01 00:00:00 J - Provincial Living 924 321.96
18743 4812 NULL NULL 11 2013-10-01 00:00:00 J - Provincial Living 224 76.36
147929 4810 NULL NULL 11 2013-10-01 00:00:00 J - Provincial Living 776 280.09
121522 4878 4.20 3.00 SS 2013-11-01 00:00:00 L - Regional Endeavours 1116 -491.04
58172 4815 4.60 5.00 11 2014-06-01 00:00:00 F - New Homes and Hopes 1740 562.98
58172 4815 4.60 5.00 SS 2013-09-01 00:00:00 F - New Homes and Hopes 1372 -603.68
44489 4812 NULL NULL 11 2014-08-01 00:00:00 J - Provincial Living 472 206.97
37137 4868 NULL NULL 33 2014-09-01 00:00:00 L - Regional Endeavours 457 92.45
94639 4870 NULL NULL 11 2013-07-01 00:00:00 J - Provincial Living 1316 378.81
94581 4868 NULL NULL 11 2014-06-01 00:00:00 G - Middle Australia 893 312.84ASKER
Hi Porlet,
Here i've changed little bit your SQL but seems not gettting same result
this sql seems not right to me, as we need in every meshgroup, check every tariff combination (in CASE condition ) then count how many in each tariff and calculate avg on that particular group. and display all tariff combination with group by meshgroup. hope u understand.
something like this
Hope you understand what we want to achieve here.
Thank you
Here i've changed little bit your SQL but seems not gettting same result
select
meshblock_group
, case when tariff_code IN(11,31,'SSA') then '11 and 31 and SA or SSA'
when tariff_code IN(11,33,'SSA') then '11 and 33 and SA or SSA'
when tariff_code IN(11,31) then '11 and 31'
when tariff_code IN(11,31) then '11 and 33'
when tariff_code = 11 then '11'
end " tariff combination"
, count(*) "Count."
, AVG(kwh_charge) "Avg Anual Spend"
from public.habidapt_2014_10_10
where tariff_code = 11
group by
meshblock_group
, case when tariff_code IN(11,31,'SSA') then '11 and 31 and SA or SSA'
when tariff_code IN(11,33,'SSA') then '11 and 33 and SA or SSA'
when tariff_code IN(11,31) then '11 and 31'
when tariff_code IN(11,31) then '11 and 33'
when tariff_code = 11 then '11'
end
order by meshblock_group;
OUTPUT
11 and 31 and SA or SSA 83169 402.90
A - Exclusive Environs 11 and 31 and SA or SSA 3426 569.87
B - Knowledgable Success 11 and 31 and SA or SSA 21914 518.35
C - Independence and Careers 11 and 31 and SA or SSA 15030 378.64
D - Affluent Acreage 11 and 31 and SA or SSA 17532 472.06
E - Distanced Existence 11 and 31 and SA or SSA 36926 530.96
F - New Homes and Hopes 11 and 31 and SA or SSA 178937 464.98
G - Middle Australia 11 and 31 and SA or SSA 73754 426.26
H - International Infusion 11 and 31 and SA or SSA 207 412.83
I - Books and Boots 11 and 31 and SA or SSA 232 350.75
J - Provincial Living 11 and 31 and SA or SSA 215615 374.35
K - Traditionally Grey 11 and 31 and SA or SSA 38236 317.30
L - Regional Endeavours 11 and 31 and SA or SSA 98207 388.74
M - Remotely Blue 11 and 31 and SA or SSA 54852 372.75
U - Unknown 11 and 31 and SA or SSA 7928 400.12this sql seems not right to me, as we need in every meshgroup, check every tariff combination (in CASE condition ) then count how many in each tariff and calculate avg on that particular group. and display all tariff combination with group by meshgroup. hope u understand.
something like this
Customer Group Tariff Combinations Count AvgAnnualSpend
A 11 1902 2,198.00
11 and 31 324 1,866.00
11 and 31 and SA or SSA 94 1,258.00
B
11 102 2,498.00
11 and 31 324 1,8122.00
11 and 31 and SA or SSA 94 1,290.00
so on Hope you understand what we want to achieve here.
Thank you
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Now, let's look at "Tariff Combinations"
here is a single record:
That single record has just one value for tariff_code, so no record can ever have 3 values in that field.
tariff_code = 11 and tariff_code=31 and tariff_code in ('SA','SSA')
here is a single record:
sno postcode pv_capacity_kw pv_panel_kw tariff_code
68476 4815 NULL NULL 33That single record has just one value for tariff_code, so no record can ever have 3 values in that field.
tariff_code = 11 and tariff_code=31 and tariff_code in ('SA','SSA')
Why did you post in Oracle, MSSQL and MySQL?