Difficult aggregation question

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)

Difficult aggregation question

Author

Topic

fuzzyip
Starting Member

35 Posts

Posted - 2008-07-18 : 14:23:02

I have the following table

Item DateSold SalePrice DateAcq MSRP City
1 7/13/2008 13:00 7/8/2008 13:00 $15,989 Pasadena, California
2 7/13/2008 9:00 35995 4/18/2008 13:32 $34,675 Austin, Texas
3 6/6/2008 20:00 $10,991 5/31/2008 20:00 $11,275 Gainesville, Florida
4 6/26/2008 11:00 4/11/2008 15:33 $34,955 Pasadena, California
5 5/2/2008 17:00 4/22/2008 16:00 $52,025 Pasadena, California
6 7/15/2008 13:10 4/11/2008 15:33 $41,400 Austin, Texas
7 5/9/2008 22:00 $78,899 5/6/2008 22:00 Gainesville, Florida
8 5/8/2008 19:00 56204 4/11/2008 15:50 $57,410 Austin, Texas

I'm basically trying to do a storedproc that will add up and avg the price based on 3 parameters, startdate, enddate, and state

I wrote this guy up,

create procedure anstats

@datebegin datetime,
@dateend datetime,
@state varchar(30)
as begin

select sum(convert(money, price)) as Revenue, avg(convert(money,price)) as Average_Price
from ANSold
where datesold >= @datebegin
and datesold < dateadd (d, +1, @dateend)
and city like '%'+@state+'%'

end

This works, but the sum and avg is aggregating the entries only in the price table.

The problem is that not everything is in the price column, for example whenever there is a blank in the price column, it means that that product was sold at the MSRP value. So it must take the value from the msrp column, but only for those that have blanks.

I think if we can fix this, we can also fix the averaging problem where it counts blanks as part of the average (eg, entry with 5 and an entry with blank, the average is 2.5 when it should be just 5)

My initial thoughts is to use some type of column join command that creates a calculated field based on my directions above. But I'm not sure of how to go about doing this.

Here's a pic of the table since its not formatting well

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 14:33:14

Try like this & see

create procedure anstats

@datebegin datetime,
@dateend datetime,
@state varchar(30)
as begin


select 
sum(CASE WHEN NULLIF(price,'') IS NULL THEN convert(money, MSRP)ELSE convert(money, price) END) as Revenue, 
avg(CASE WHEN NULLIF(price,'') IS NULL THEN convert(money, MSRP)ELSE convert(money, price) END) as Average_Price
from ANSold
where datesold >= @datebegin
and datesold < dateadd (d, +1, @dateend)
and city like '%'+@state+'%'


end

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-18 : 14:41:36

or use COALESCE

http://msdn.microsoft.com/en-us/library/ms190349.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources