Aggregate a column by different group by clauses

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)

Aggregate a column by different group by clauses

Author

Topic

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-08-04 : 02:13:31

Hello All,

I have a temp table in my SP. The sample data set is populated below. I wanted to write a query to show the aggregate quantity across all the regions but the group by clause is different based on the PartType.

Can anyone give me suggestions on how to start SQL to achieve this result?

The final result should be-

If Parttype = 1 THEN
Group by PartDesc, ItemNo, MfgItemNo and Attribute to get the Sum of Qty and distinct count of Region and minimum publisheddays. The Summary column should concatenate the Region + PartNo for all the lines which aggregated the Qty.

If Parttype = 2 THEN
Group by PartDesc, ItemNo and Attribute to get the Sum of Qty and distinct count of Region and minimum publisheddays. The Summary column should concatenate the Region + PartNo for all the lines which aggregated the Qty.


#TempQty table

Region	PartNo	PartDesc    	      ItemNo     MfgItemNo    Qty	 PublishedDays    Attribute    PartType
1       A0001   CarbMonoOxide         202        M202         100         12			1	  1
0       A1001   CarbMonoOxide         202        M202         200         08			1         1
1       A1001   CarbMonoOxide         202        M122         100         40			1	  1

4       A5001   Menthol	              452        M452         200         11			1	  2
4       A6001   Menthol	              450        M450         300         43			2	  2
0       A6001   Menthol	              450        M450         200         11			2	  2

2       A7801   Acetone	              452        M452         800         99			1	  2

Final Results - 
PartDesc 	TotalQty	RegionCount	PublishedDays		Attribute	SummaryDesc     	
CarbMonoOxide     300            2		08 (minimum)		1		1:A0001,0:A1001
CarbMonoOxide     100            1		40			1		1:A1001
Menthol           200   	 1              11 		        1		4:A5001
Menthol           500   	 2              11 (minimum)	        2		4:A6001, 0:A6001
Acetone           800   	 1              99		        1		2:A7801

Thanks,
-S

saran_d28
Starting Member

36 Posts

Posted - 2009-08-04 : 02:43:26

Hi try this,

select sum(qty), min(publisheddays), ...
from #TempQty where parttype =1
group by PartDesc, ItemNo, MfgItemNo , attitude
union all
select sum(qty), min(publisheddays), ...
from #TempQty where parttype =2
group by PartDesc, ItemNo, attitude

OR

select sum(qty), min(publisheddays), ...
from #TempQty
group by
(case when parttype =1 then PartDesc, ItemNo, MfgItemNo , attitude
else PartDesc, ItemNo, attitude end)

--Saravanan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-04 : 02:44:31

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

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-08-04 : 02:59:09

Thank you KH. Your solution is really very close to what I am looking for except for another CASE for Parttype = 2.

If PartType = 2 then the group by is different. I will have to change the GROUP BY and the STUFF function to include the PartType = 2 condition. Once I do that I should be good to go.

Awesome query!

Thanks again.

-S

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-08-04 : 03:02:31

One more question KH,

Can I modify the existing query or should I write union all and have the same query for PartType = 2?

Thanks,
-S

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 03:18:16

[code]DECLARE @Sample TABLE
(
Region int,
PartNo varchar(5),
PartDesc varchar(15),
ItemNo int,
MfgItemNo varchar(5),
Qty int,
PublishedDays int,
Attribute int,
PartType int
)

INSERT @Sample
SELECT 1, 'A0001', 'CarbMonoOxide', 202, 'M202', 100, 12, 1, 1 UNION ALL
SELECT 0, 'A1001', 'CarbMonoOxide', 202, 'M202', 200, 08, 1, 1 UNION ALL
SELECT 1, 'A1001', 'CarbMonoOxide', 202, 'M122', 100, 40, 1, 1 UNION ALL
SELECT 4, 'A5001', 'Menthol', 452, 'M452', 200, 11, 1, 2 UNION ALL
SELECT 4, 'A6001', 'Menthol', 450, 'M450', 300, 43, 2, 2 UNION ALL
SELECT 0, 'A6001', 'Menthol', 450, 'M450', 200, 11, 2, 2 UNION ALL
SELECT 2, 'A7801', 'Acetone', 452, 'M452', 800, 99, 1, 2

SELECT s.PartDesc,
SUM(s.Qty) AS TotalQty,
COUNT(DISTINCT s.Region) AS RegionCount,
MIN(s.PublishedDays) AS PublishedDays,
s.Attribute,
STUFF(f.d, 1, 1, '') AS SummaryDesc
FROM @Sample AS s
CROSS APPLY (
SELECT ',' + CAST(Region AS VARCHAR(12)) + ':' + PartNo
FROM @Sample AS w
WHERE w.MfgItemNo = s.MfgItemNo
AND w.PartDesc = s.PartDesc
ORDER BY PartNo,
Region DESC
FOR XML PATH('')
) AS f(d)
WHERE s.PartType = 1
GROUP BY s.PartDesc,
s.ItemNo,
s.MfgItemNo,
s.Attribute,
f.d

UNION ALL

SELECT s.PartDesc,
SUM(s.Qty) AS TotalQty,
COUNT(DISTINCT s.Region) AS RegionCount,
MIN(s.PublishedDays) AS PublishedDays,
s.Attribute,
STUFF(f.d, 1, 1, '') AS SummaryDesc
FROM @Sample AS s
CROSS APPLY (
SELECT ',' + CAST(Region AS VARCHAR(12)) + ':' + PartNo
FROM @Sample AS w
WHERE w.MfgItemNo = s.MfgItemNo
AND w.PartDesc = s.PartDesc
ORDER BY PartNo,
Region DESC
FOR XML PATH('')
) AS f(d)
WHERE s.PartType = 2
GROUP BY s.PartDesc,
s.ItemNo,
s.Attribute,
f.d[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-04 : 03:24:09

quote:
Originally posted by sqlpal2009

Thank you KH. Your solution is really very close to what I am looking for except for another CASE for Parttype = 2.

If PartType = 2 then the group by is different. I will have to change the GROUP BY and the STUFF function to include the PartType = 2 condition. Once I do that I should be good to go.

Awesome query!

Thanks again.

-S

i think it will work fine for parttype 2 also . you just need to include itemno also in group by


SELECT	PartDesc,ItemNo,
	TotalQty	= SUM(Qty),
	RegionCount	= COUNT(DISTINCT Region),
	PublishedDays	= MIN(PublishedDays),
	Attribute,
	SummaryDesc	= STUFF((SELECT ',' + CONVERT(varchar(5), x.Region) + ':' + x.PartNo
					FROM 	#TempQty x
					WHERE 	x.PartDesc 	= t.PartDesc 
					AND	x.Attribute	= t.Attribute
AND	x.ItemNo	= t.ItemNo

					AND	(CASE WHEN x.PartType = 1 THEN x.MfgItemNo ELSE '' END) 
					= 	(CASE WHEN t.PartType = 1 THEN t.MfgItemNo ELSE '' END)
					ORDER BY ',' + CONVERT(varchar(5), x.Region)
				FOR XML PATH('')), 1, 1, '')
FROM	#TempQty t
GROUP BY PartDesc,ItemNo,Attribute, CASE WHEN PartType = 1 THEN MfgItemNo ELSE '' END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-04 : 03:53:56

quote:
Originally posted by sqlpal2009

Thank you KH. Your solution is really very close to what I am looking for except for another CASE for Parttype = 2.

If PartType = 2 then the group by is different. I will have to change the GROUP BY and the STUFF function to include the PartType = 2 condition. Once I do that I should be good to go.

Awesome query!

Thanks again.

-S

you mean that query does not handle PartType = 2 ? It does not produce the result that you want ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-04 : 04:07:43

[code]
CREATE TABLE #TempQty
(
Region int,
PartNo varchar(5),
PartDesc varchar(15),
ItemNo int,
MfgItemNo varchar(5),
Qty int,
PublishedDays int,
Attribute int,
PartType int
)
INSERT INTO #TempQty
SELECT 1, 'A0001', 'CarbMonoOxide', 202, 'M202', 100, 12, 1, 1 UNION ALL
SELECT 0, 'A1001', 'CarbMonoOxide', 202, 'M202', 200, 08, 1, 1 UNION ALL
SELECT 1, 'A1001', 'CarbMonoOxide', 202, 'M122', 100, 40, 1, 1 UNION ALL
SELECT 4, 'A5001', 'Menthol', 452, 'M452', 200, 11, 1, 2 UNION ALL
SELECT 4, 'A6001', 'Menthol', 450, 'M450', 300, 43, 2, 2 UNION ALL
SELECT 0, 'A6001', 'Menthol', 450, 'M450', 200, 11, 2, 2 UNION ALL
SELECT 2, 'A7801', 'Acetone', 452, 'M452', 800, 99, 1, 2

SELECT PartDesc,
TotalQty = SUM(Qty),
RegionCount = COUNT(DISTINCT Region),
PublishedDays = MIN(PublishedDays),
Attribute,
SummaryDesc = STUFF((SELECT ',' + CONVERT(varchar(5), x.Region) + ':' + x.PartNo
FROM #TempQty x
WHERE x.PartDesc = t.PartDesc
AND x.ItemNo = t.ItemNo
AND x.Attribute = t.Attribute
AND (CASE WHEN x.PartType = 1 THEN x.MfgItemNo ELSE '' END)
= (CASE WHEN t.PartType = 1 THEN t.MfgItemNo ELSE '' END)
ORDER BY ',' + CONVERT(varchar(5), x.Region) + ':' + x.PartNo
FOR XML PATH('')), 1, 1, '')
FROM #TempQty t
GROUP BY PartDesc, ItemNo, Attribute, CASE WHEN PartType = 1 THEN MfgItemNo ELSE '' END

-- Using CTE or derived query to translate the MfgItemNo
;WITH data (Region, PartNo, PartDesc, ItemNo, MfgItemNo, Qty, PublishedDays, Attribute, PartType)
AS
(
SELECT Region, PartNo, PartDesc, ItemNo,
MfgItemNo = CASE WHEN PartType = 1 THEN MfgItemNo ELSE '' END,
Qty, PublishedDays, Attribute, PartType
FROM #TempQty
)
SELECT PartDesc,
TotalQty = SUM(Qty),
RegionCount = COUNT(DISTINCT Region),
PublishedDays = MIN(PublishedDays),
Attribute,
SummaryDesc = STUFF((SELECT ',' + CONVERT(varchar(5), x.Region) + ':' + x.PartNo
FROM data x
WHERE x.PartDesc = t.PartDesc
AND x.ItemNo = t.ItemNo
AND x.Attribute = t.Attribute
AND x.MfgItemNo = t.MfgItemNo
ORDER BY ',' + CONVERT(varchar(5), x.Region) + ':' + x.PartNo
FOR XML PATH('')), 1, 1, '')
FROM data t
GROUP BY PartDesc, ItemNo, Attribute, MfgItemNo

/*
PartDesc TotalQty RegionCount PublishedDays Attribute SummaryDesc
--------------- ----------- ----------- ------------- ----------- ----------------
CarbMonoOxide 300 2 8 1 0:A1001,1:A0001
CarbMonoOxide 100 1 40 1 1:A1001
Menthol 200 1 11 1 4:A5001
Menthol 500 2 11 2 0:A6001,4:A6001
Acetone 800 1 99 1 2:A7801

(5 row(s) affected)
*/

[/code]

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

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-08-04 : 08:34:35

Great KH!
Your solution worked.

Hi Peso,
I tried your solution as well and that also gives the same result set. Thank you so much.

I will execute both the solutions under execution plan to see which one would be more efficient.

Thanks guys.
-S

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-08-04 : 08:51:59

Guys,

Which book do you refer for SQL 2005 development? I have to keep myself up to date with 2005 and 2008 as I still use the SQL 2000 functions in my development. So my code becomes very bulky even though it gives the same results.

Honestly I was not aware of the CROSS APPLY and FOR XML PATH functions.

I would really appreciate your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 09:02:48

The INSIDE SQL SERVER series is a good start.

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

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-08-04 : 09:11:23

You mean Inside Microsoft® SQL Server™ 2005: T-SQL Programming Peso?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 09:26:25

Yes.

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

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-08-04 : 10:24:27

Thank you Peso -;)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-04 : 10:26:08

How did your tests go?

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

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-08-13 : 10:48:59

Hi Peso,

So far the results are as expected but the user wants the RegionCount by PartName which I am not even selecting in my final select. I have it in my temp table though. How tdo I write a subquery to get the regioncount by regoin but group by only the PartName?

Thanks,
-S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources