Solved

case statement in complex SQL

Posted on 2014-11-25
9
191 Views
Last Modified: 2014-12-01
Hi Expert,

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…

Open in new window


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
0
Comment
Question by:adnankh
  • 4
  • 3
9 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40466485
I can't open the attachment.
Why did you post in Oracle, MSSQL and MySQL?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40466577
A SQL query should not suppress the repetition of Customer Group as if it was a report.

A SQL query using CASE EXPRESSIONS and GROUP BY is probably adequate for this need but it will produce an output like this:
Customer Group  Tariff Combinations         Count       AvgAnnualSpend
A               11                          1902        2,198.00 
A               11 and 31                   324         1,866.00 
A               11 and 31 and SA or SSA     94          1,258.00 
B
...

Open in new window

But you have not supplied any details about the tables or fields so I'm not sure how to answer.

For example, how relevant is this guess?
select
      customer_group
    , case when tariff1 = 11 and tariff2=31 and tariff3 in ('SA','SSA') then '11 and 31 and SA or SSA'
           when tariff1 = 11 and tariff2=31                             then '11 and 31'
           when tariff1 = 11                                            then '11'
      end
    , count(*)
    , AVG(AnnualSpend)
from some_tables 
where tariff1 = 11
group by
      customer_group
    , case when tariff1 = 11 and tariff2=31 and tariff3 in ('SA','SSA') then '11 and 31 and SA or SSA'
           when tariff1 = 11 and tariff2=31                             then '11 and 31'
           when tariff1 = 11                                            then '11'
      end

Open in new window

0
 

Author Comment

by:adnankh
ID: 40466580
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
0
 

Author Comment

by:adnankh
ID: 40466581
PortletPaul I'll try this and get back to you..Thank you
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:adnankh
ID: 40466599
Hi Porlet, I change your query and this is what I'm getting.
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.12

Open in new window


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 :)

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.84

Open in new window

0
 

Author Comment

by:adnankh
ID: 40467603
Hi Porlet,
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.12

Open in new window


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
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 

Open in new window


Hope you understand what we want to achieve here.

Thank you
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 40468065
"A SQL query should not suppress the repetition of Customer Group as if it was a report."

You are trying to make SQL into a report generator - that is not the correct way to use SQL.

SQL will repeat the Customer Group on every row.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40468089
Now, let's look at "Tariff Combinations"

here is a single record:
sno   postcode  pv_capacity_kw  pv_panel_kw     tariff_code
68476 4815      NULL            NULL            33

Open in new window


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')
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now