Query optimize with select case

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query optimize with select case

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2014-01-25 : 06:51:03

Team I have table structure like

Name	Age 	Type	Mark1	Mark2	Mark3	Mark4	Year	Region	Season	Group
										
Mark	15	Yearly	80	23	86	85	2001	South	Winter	Permanent
Steve	16	Quaterly	70	66	56	95	2001	North	Summer	Consultant
Paul	21	Yearly	95	56	59	88	2012	West	Other	Contract
Prem	20	Yearly	54	26	68	63	2013	South	Winter	Consultant
Ram	20	Monthly	52	23	56	67	2012	North	Other	Permanent
John	19	Others	65	56	46	97	2013	East	Other	Contract
papa	25	Monthly	54	55	55	48	2003	East	Winter	Consultant
Ragul	24	Yearly	85	5	58	84	2006	North	Summer	Consultant

And my query is like

Select name, Age 
       case when [Type]='Yearly' Then Mark4 as Yearcount,
       case when [Type]='Quaterly' Then Mark3 as QCount,
       case when [Type] = 'Monthly' Then Mark1 as MonthCount
Group by Region, Season

Union

Select name, Age 
       case when [Type]='Yearly' Then Mark4 as Yearcount,
       case when [Type]='Quaterly' Then Mark3 as QCount,
       case when [Type] = 'Monthly' Then Mark1 as MonthCount
Group by Season, Group

How this query can be optimized.

Thanks in Advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-25 : 08:22:31

Is that the full query ?

What is the required result ?

KH
[spoiler]Time is always against us[/spoiler]

danasegarane76
Posting Yak Master

242 Posts

Posted - 2014-01-25 : 09:36:35

Select name,age, sum(Mark4) as YearCount,sum(Mark3) as QuaterCount, sum(Mark1) as MonthCount from (Select name, Age
case when [Type]='Yearly' Then Mark4 as Yearcount,
case when [Type]='Quaterly' Then Mark3 as QCount,
case when [Type] = 'Monthly' Then Mark1 as MonthCount
Group by Region, Season

Union

Select name, Age
case when [Type]='Yearly' Then Mark4 as Yearcount,
case when [Type]='Quaterly' Then Mark3 as QCount,
case when [Type] = 'Monthly' Then Mark1 as MonthCount
Group by Season, Group
) a

Is there any way to change this part

case when [Type]='Yearly' Then Mark4 as Yearcount,
case when [Type]='Quaterly' Then Mark3 as QCount,
case when [Type] = 'Monthly' Then Mark1 as MonthCount

as sub and call from main query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-25 : 10:08:58

Your query will not execute.

Maybe you can explain what are you trying to achieve and also post the what is the required result

KH
[spoiler]Time is always against us[/spoiler]

danasegarane76
Posting Yak Master

242 Posts

Posted - 2014-01-25 : 10:41:41

Basically I want to convert the rows into columns. As I cannot provide real query and real data, I am posting with some data.

The table will be separted by few groups for example here(group 1 : Region, Season and group 2 : Season, Group).

Then these grouped values are to summed using common columns.

And the result will be something like


Name	Age 	YearCount  Qcount   MonthCount
										
Mark	15	     85
Steve	16	                 66
Paul	21	    88
Prem	20	    63
Ram	    20	                          52
John	19	    
papa	25	                          54
Ragul	24	   84

And then I need sum these two result set to get the total sum of Yearcount, Qcount and Month

I hope I explained about the requirement

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-25 : 18:38:31

[code]
select Name, Age,
case when [Type] = 'Yearly' Then Mark4 end as YearCount,
case when [Type] = 'Quaterly' Then Mark3 end as QCount,
case when [Type] = 'Monthly' Then Mark1 end as MonthCount
from yourtable
[/code]

KH
[spoiler]Time is always against us[/spoiler]

danasegarane76
Posting Yak Master

242 Posts

Posted - 2014-01-25 : 18:56:09

I need to group the split the main table using few groups and then get the sum by joining them. Or like this with some some other tables names

select Name, Age,
case when [Type] = 'Yearly' Then Mark4 end as YearCount,
case when [Type] = 'Quaterly' Then Mark3 end as QCount,
case when [Type] = 'Monthly' Then Mark1 end as MonthCount
from table1

Union

select Name, Age,
case when [Type] = 'Yearly' Then Mark4 end as YearCount,
case when [Type] = 'Quaterly' Then Mark3 end as QCount,
case when [Type] = 'Monthly' Then Mark1 end as MonthCount
from Table2

Join

select Name, Age,
case when [Type] = 'Yearly' Then Mark4 end as YearCount,
case when [Type] = 'Quaterly' Then Mark3 end as QCount,
case when [Type] = 'Monthly' Then Mark1 end as MonthCount
from Table3

Can it be simplified.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-25 : 20:16:57

if you have data in 3 tables, you can


select Name, Age,
sum(case when [Type] = 'Yearly' Then Mark4 end) as YearCount,
sum(case when [Type] = 'Quaterly' Then Mark3 end) as QCount,
sum(case when [Type] = 'Monthly' Then Mark1 end) as MonthCount
from 
(
    select * from table1
union all
    select * from table2
union all
    select * from table3
) t
group by Name, Age