Many aggregates in one 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 2008 Forums

Transact-SQL (2008)

Many aggregates in one query

Author

Topic

sauce1979
Starting Member

47 Posts

Posted - 2012-05-08 : 20:45:21

I have the following tables:

Products
(Product_id,Product_Name,Product_Type,Cost_Price)

Sales
(SalesOrder_id, Product_id,Quantity, Sell_Price, Sale_Date)

I want to produce a query that provides most profitable products, per year,week,month,day and overall.

I can do these queries individually for example the top 5 profitable products per month


SELECT TOP 5 P.Product_Name,DATEPART(MM,S.SaleDate) AS MoNTH(S.Sell_Price P.Cost_Price) * S.Quantity AS Profit
FROM Products P INNER JOIN Sales S
ON S.Product_id = P.Product_id
GROUP BY P.Product_Name,DATEPART(MM,S.SaleDate)

Is there a way to produce an output with profitable products per week, year etc in one query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-08 : 21:04:06

What is the expected result like ?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources