need help to built 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

SQL Server 2005 Forums

Transact-SQL (2005)

need help to built query

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-05-19 : 08:50:01

ffg

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-19 : 08:55:59

[code]
select c.CategoryID, c.CategoryName, sum(Sales * isnull(Factor, 1))
from #Categories c
inner join #Product p on c.CategoryID = p.CategoryID
left join #ProductFactors f on p.ProductID = f.ProductID
group by c.CategoryID, c.CategoryName
having avg(Sales) > 5
[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-19 : 09:00:03

quote:
Originally posted by Delinda

ffg

Why are you keep removing your original question ?
you did it once here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=144807

quote:

I've as follow,

create table #Product (ProductID int, CategoryID int, Sales money)

create table #Categories (CategoryID int, CategoryName nvarchar(100))

create table #ProductFactors (ProductID int, Factor money)

insert into #Categories values (1, 'CAT1')

insert into #Categories values (2, 'CAT2')

insert into #Product values (1,1,5)

insert into #Product values (2,1,7)

insert into #Product values (3,2,2)

insert into #Product values (4,2,8)

insert into #Product values (5,2,4)

insert into #ProductFactors values (2,1.5)

insert into #ProductFactors values (3,1.2)

insert into #ProductFactors values (5,1.8)

select * from #Categories;select * from #Product;select * from #ProductFactors

drop table #Product,#Categories,#ProductFactors

-- Some of the products have factors which must first be multiplied by their sales.

-- If no factor exists for a product you should use factor of 1.

-- The result should contain only categories that have average sales larger than 5.

How to use single statement to product as follow,

CategoryID CategoryName TotalFactoredSales

----------- ------------ ---------------------

1 CAT1 15.50