Multiple grouping with sum and max

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)

Multiple grouping with sum and max

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-05-29 : 05:23:16

TableA
ID NO Quantity Co3 Co4
11 1 33 23 19
11 2 45 56 2
11 3 56 79 1
11 2 78 54 6
22 1 87 45 3
33 2 65 84 9
44 1 45 13 5
44 2 789 500 16
44 3 87 21 28
44 2 4 13 30
I wish to show out sum(quantity) and max(Co3) when NO = 2 and max(Co4) when NO = 3 how can i do it??

sql code
---------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableA](
[ID] [int] NULL,
[NO] [int] NULL,
[Quantity] [int] NULL,
[Co3] [int] NULL,
[Co4] [nchar](10) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[TableA]([ID], [NO], [Quantity], [Co3], [Co4])
SELECT 11, 1,33,23,19 UNION ALL
SELECT 11,2,45,56,2 UNION ALL
SELECT 11,3,56,19,1 UNION ALL
SELECT 11,2,78,54,6 UNION ALL
SELECT 22,1,87,45,3 UNION ALL
SELECT 33,2,65,84,9 UNION ALL
SELECT 44,1,45,13,5 UNION ALL
SELECT 44,2,789,500,16 UNION ALL
SELECT 44,3,87,21,28 UNION ALL
SELECT 44,2,4,13,30
Go

My test query which does work
select m.ID, a.Quantity, b.Co3, c.Co4 from
(((select ID, sum(quantity) as quantity
from TableA
group by ID)a
full join
(SELECT ID, MAX(Co3) as Co3
from TableA
where No = 2
group by ID)b on a.ID=b.ID)
full join
(SELECT ID, MAX(Co4) as Co4
from TableA
where No IN 3
group by ID)c on b.ID=c.ID)

Result enquiry
11,171,56,1
22,1,87,45,3
33,2,65,84,9
44,925,21,30

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 05:30:14

[code]SELECT ID,
SUM(Quantity),
MAX(CASE WHEN No = 2 THEN Co3 ELSE NULL END),
MAX(CASE WHEN No = 3 THEN Co4 ELSE NULL END)
FROM Table1
GROUP BY ID[/code]

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-05-29 : 05:33:49

urs result...
11 290 56 1
22 87 NULL NULL
33 65 84 NULL
44 925 500 28

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 05:40:24

Yes. There are no records for ID 22 with "NO" either 2 or 3.

Beacuse you result is inverted for ID 44. You take max of Co4 then no = 2,
but for ID 11 you take max of co4 when no = 3!

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-05-29 : 05:41:37

woops i forgotten...thx peso u r the great...hop u get urs mvp xD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 05:44:41

Thank you.

I don't think your query works. It doesn't parse correctly.

quote:
Originally posted by waterduck

My test query which does work
select m.ID, a.Quantity, b.Co3, c.Co4 from
(((select ID, sum(quantity) as quantity
from TableA
group by ID)a
full join
(SELECT ID, MAX(Co3) as Co3
from TableA
where No = 2
group by ID)b on a.ID=b.ID)
full join
(SELECT ID, MAX(Co4) as Co4
from TableA
where No IN 3
group by ID)c on b.ID=c.ID)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 05:48:39

If I change your query so it does parse, you get same result as my suggestion.

select		a.ID,
		a.Quantity,
		b.Co3,
		c.Co4
from		(
			select		ID,
					sum(quantity) as quantity
			from		@TableA
			group by	ID
		) AS a
full join	(
			SELECT		ID,
					MAX(Co3) as Co3
			from		@TableA
			where		No = 2
			group by	ID
		) AS b on a.ID=b.ID
full join	(
			SELECT		ID,
					MAX(Co4) as Co4
			from		@TableA
			where		No = 3
			group by	ID
		) AS c on b.ID=c.ID

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources