Group quantity on product and month

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 2000 Forums

SQL Server Development (2000)

Group quantity on product and month

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-28 : 07:44:29

Pelle writes "Hi,
I have a table with three fields: product, quantity and a date
I would like to write a question thats shows the quantity for each product summarized per month.

If the data looks like this:


product     quantity       date
------------------------------------
prod1       100            2005-01-01
prod1       50             2005-01-01
prod1       70             2005-02-01
prod2       10             2005-02-02

I would like the result to look like this, where the columns represent month 1 - 12


prod1 150 70 0 0 0 0 0 0 0 0 0 0
prod2   0 10 0 0 0 0 0 0 0 0 0 0

Thanks!
Pelle"

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-28 : 11:36:28

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(product varchar(15), quantity int, [date] datetime)
GO

INSERT INTO myTable99(product, quantity, [date])
SELECT 'prod1', 100, '2005-01-01' UNION ALL
SELECT 'prod1', 50, '2005-01-01' UNION ALL
SELECT 'prod1', 70, '2005-02-01' UNION ALL
SELECT 'prod2', 10, '2005-02-02'
GO

SELECT Product
, SUM(JAN) AS SUM_JAN
, SUM(FEB) AS SUM_FEB
-- , ect
FROM (SELECT Product
, CASE WHEN DATEPART(mm,[date]) = 1 THEN quantity ELSE 0 END AS JAN
, CASE WHEN DATEPART(mm,[date]) = 2 THEN quantity ELSE 0 END AS FEB
-- , ect
FROM myTable99) AS XXX
GROUP BY Product
GO

DROP TABLE myTable99
GO

[/code]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources