query with sub 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 2008 Forums

Transact-SQL (2008)

query with sub grouping

Author

Topic

Dargon
Starting Member

26 Posts

Posted - 2014-01-17 : 11:28:20

Hi all,

I am stuck in the query which looks pretty simple:

I have data:
DECLARE @t TABLE(seq INT, prd VARCHAR(5), Qty float )
INSERT INTO @t( seq, prd, Qty )VALUES ( 1, 'A', 2)
INSERT INTO @t( seq, prd, Qty )VALUES ( 2, 'A', 3)
INSERT INTO @t( seq, prd, Qty )VALUES ( 3, 'B', 4)
INSERT INTO @t( seq, prd, Qty )VALUES ( 4, 'C', 5)
INSERT INTO @t( seq, prd, Qty )VALUES ( 5, 'C', 6)
INSERT INTO @t( seq, prd, Qty )VALUES ( 6, 'A', 4)
INSERT INTO @t( seq, prd, Qty )VALUES ( 7, 'C', 1)

The desired output is :
seq prd Qty
1 A 5
2 B 4
3 C 11
4 A 4
5 C 1

I tried to use rollup with grouping but can't produce the right result. Please, help!

Thanks,

Dargon

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-01-17 : 13:23:42

If I understood you correctly, I am not sure if rollup would do what you are looking for. See if this works for you - if not, can you post more representative data, or the rule that you want to use?

SELECT
 	ROW_NUMBER() OVER (ORDER BY MIN(seq)) AS seq,
	prd,SUM(Qty)
FROM
(
SELECT
	*,
	seq-ROW_NUMBER() OVER (PARTITION BY prd ORDER BY seq) AS seq2
FROM
	@t
)s GROUP BY prd, seq2
ORDER BY MIN(seq)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources