Help with Grouping - 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)

Help with Grouping

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-02-01 : 11:04:36

I am trying to show volume based on a date range and product, grouped by symbol and that product.

Similar to the below suedo code:

select symbol, product, sum(volume), end_date from #test_table
if year(end_date) is 2007 and product = prodA, show volume grouped by symbol for prodA
if year(end_date) is 2008 and product = prodA, show volume grouped by symbol for prodA
if year(end_date) is 2007 and product = prodB, show volume grouped by symbol for prodB
if year(end_date) is 2008 and product = prodB, show volume grouped by symbol for prodB

I have attached a sample temp table structure below that can be used to show sample data.

Hope this makes sense, any help would be much appreciated.
Thanks
Qman

CREATE TABLE #test_table (symbol varchar(3), product varchar(30), volume int, end_date datetime)

INSERT INTO #test_table
(symbol, product, volume, end_date)
select '1', 'prodA' , 10 , '2007-01-01'
UNION ALL
select '1', 'prodA' , 20 , '2007-02-22'
UNION ALL
select '1', 'prodB' , 10 , '2008-02-26'
UNION ALL
select '1', 'prodB' , 30 , '2008-04-25'
UNION ALL
select '15' , 'prodA' , 100 , '2007-12-25'
UNION ALL
select '15' , 'prodA' , 20 , '2007-01-13'
UNION ALL
select '15' , 'prodB' , 100 , '2008-01-11'
UNION ALL
select '15' , 'prodB' , 50 , '2008-04-12'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-01 : 11:29:31

This?

CREATE TABLE #test_table (symbol varchar(3), product varchar(30), volume int, end_date datetime)

INSERT INTO #test_table
(symbol, product, volume, end_date)
select '1', 'prodA' , 10 , '2007-01-01'
UNION ALL
select '1', 'prodA' , 20 , '2007-02-22'
UNION ALL
select '1', 'prodB' , 10 , '2008-02-26'
UNION ALL
select '1', 'prodB' , 30 , '2008-04-25'
UNION ALL
select '15' , 'prodA' , 100 , '2007-12-25'
UNION ALL
select '15' , 'prodA' , 20 , '2007-01-13'
UNION ALL
select '15' , 'prodB' , 100 , '2008-01-11'
UNION ALL
select '15' , 'prodB' , 50 , '2008-04-12'

--select * from #test_table

select symbol,product,sum(volume) as volume, year(end_date) as YearEndDate
from #test_table
group by symbol,product,year(end_date)

drop table #test_table

No, you're never too old to Yak'n'Roll if you're too young to die.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-02-01 : 11:51:35

Thanks for the help, going further.....

How can I show the 2007 volume and 2008 volume for each product on the same line, grouped by symbol (only one symbol per line)?

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-01 : 11:54:20

You can look in BOL for PIVOT for example but I am not familiar with it...sorry.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources