How to optimize this query? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

How to optimize this query?

Author

Topic

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2009-01-20 : 11:27:03

Hi All,

I am retriving 5 values from a single table but using 3 differnt queries. Can some body please let me know how to get all the values in a single query?

The following are my queries:

select datename(month,@startdate) as Monthname, count(*) as Total_Records_Month, Sum(Certificate_Fee) as TotalAmountCollected from Table1 where submit_date>=@StartDate and submit_date<=@Enddate

select count(*) as Total_Count_55 from Table1
where submit_date>=@StartDate and submit_date<=@Enddate and fee=55

select count(*) as Total_Count_25 from Table1 where submit_date>=@StartDate and submit_date<=@Enddate and fee=25

Your help will be very much appreciated.This is very urgent

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-01-20 : 11:33:18

[code]select
datename(month,@startdate) as Monthname,
count(*) as Total_Records_Month,
Sum(Certificate_Fee) as TotalAmountCollected
sum(case when fee = 55 then 1 else 0 end) as Total_Count_55,
sum(case when fee = 25 then 1 else 0 end) as Total_Count_25
from Table1
where submit_date>=@StartDate and submit_date<=@Enddate[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:33:40

[code]
select datename(month,@startdate) as Monthname,
count(*) as Total_Records_Month,
Sum(Certificate_Fee) as TotalAmountCollected,
t2.Total_Count_55,t2.Total_Count_25
from Table1 t1
cross join (select sum(case when fee=55 then 1 else 0 end) as Total_Count_55,sum(case when fee=25 then 1 else 0 end) as Total_Count_25 from Table1
where submit_date>=@StartDate and submit_date<=@Enddate )t2
where submit_date>=@StartDate and submit_date<=@Enddate
group by datename(month,@startdate),t2.Total_Count_55,t2.Total_Count_25
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:34:30

quote:

Originally posted by harsh_athalye

select 
datename(month,@startdate) as Monthname, 
count(*) as Total_Records_Month, 
Sum(Certificate_Fee) as TotalAmountCollected 
sum(case when fee = 55 then 1 else 0 end) as Total_Count_55,
sum(case when fee = 25 then 1 else 0 end) as Total_Count_25
from Table1 
where submit_date>=@StartDate and submit_date<=@Enddate

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

this will not work as for using Monthname with aggregates you need to group by it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:42:06

quote:
Originally posted by harsh_athalye

select
datename(month,@startdate) as Monthname,
count(*) as Total_Records_Month,
Sum(Certificate_Fee) as TotalAmountCollected
sum(case when fee = 55 then 1 else 0 end) as Total_Count_55,
sum(case when fee = 25 then 1 else 0 end) as Total_Count_25
from Table1
where submit_date>=@StartDate and submit_date<=@Enddate

group by datename(month,@startdate)

Edit: Thanks Visakh!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

still this wont give what OP asked. the posted queries were giving total values in table for fee=55 & fee=25 but the above query will give only split ups of total count based on month.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-01-20 : 11:46:28

quote:
Originally posted by visakh16

quote:
Originally posted by harsh_athalye

select
datename(month,@startdate) as Monthname,
count(*) as Total_Records_Month,
Sum(Certificate_Fee) as TotalAmountCollected
sum(case when fee = 55 then 1 else 0 end) as Total_Count_55,
sum(case when fee = 25 then 1 else 0 end) as Total_Count_25
from Table1
where submit_date>=@StartDate and submit_date<=@Enddate

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

this will not work as for using Monthname with aggregates you need to group by it

Visakh,

You are missing a point here. We don't need group by since datename() function is applied on the variable, and not a table column.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:49:19

oh...yes...you're right. i thought it was table field. but seeing the column alias i think it should be


select 
datename(month,submit_date) as Monthname, 
count(*) as Total_Records_Month, 
Sum(Certificate_Fee) as TotalAmountCollected 
sum(case when fee = 55 then 1 else 0 end) as Total_Count_55,
sum(case when fee = 25 then 1 else 0 end) as Total_Count_25
from Table1 
where submit_date>=@StartDate and submit_date<=@Enddate
group by datename(month,submit_date)

RaghaSM
Yak Posting Veteran

52 Posts

Posted - 2009-01-20 : 11:57:12

Harsh and Visakh,

Thanks a lot for ur help. Both the queries are working fine.

Ragha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 12:01:21

welcome. b/w what was your actually intented one?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources