T-SQL Group By question - 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)

T-SQL Group By question

Author

Topic

jjw
Starting Member

9 Posts

Posted - 2012-02-07 : 12:00:21

I'm trying to figure out how to group by some conditional business logic. I have Account Definitions and a summary table with metric data. Account Definitions are broken down into LOB (Line of Business) sub-groups.
The key here is that 7 LOB's make up a larger logical group called "Merchandise". All 7 LOB's need to be defined (but not necessarily have data) in order to be summed up as Merchandise. If a LOB is not one of the 7, then it should be listed uniquely.
7 LOB's that make up Merchandise = 'AG','CH','EM','FC','MT','PA','PF')

Here is the DDL for the 2 tables used
create table accountdefinition (account varchar(200),lob varchar(20))
Create table summary (account varchar(200),lob varchar(20),volume1 int,volume2 int, volume3 int)

Here is some sample data for the 2 tables

insert into accountdefinition (account,lob)
select 'Agway','AG' union all
select 'Agway','CH' union all
select 'Agway','CO' union all
select 'Agway','EM' union all
select 'Agway','FC' union all
select 'Agway','PA' union all
select 'Agway','PF' union all
select 'Cerestar','AG' union all
select 'Cerestar','CH' union all
select 'Cerestar','CO' union all
select 'Cerestar','EM' union all
select 'Cerestar','FC' union all
select 'Cerestar','MT' union all
select 'Cerestar','PA' union all
select 'Cerestar','PF'

insert into summary (account,lob,volume1,volume2,volume3)
select 'Agway','AG',2,3,55 union all
select 'Agway','AG',3,2,45 union all
select 'Cerestar','CO',1,1,100 union all
select 'Cerestar','EM',1,1,200

Here is the query I wrote
[CODE]select
ad.account,
'Merchandise' AS LOB,
SUM(ac.volume1) as Volume_PYT,
SUM(ac.volume2) as Volume_YTD,
SUM(ac.volume3) as Volume_CY

from accountdefinition ad
left join SUMMARY ac
on ad.account = ac.account
and ad.lob = ac.lob

where ad.lob in ('AG','CH','EM','FC','MT','PA','PF')
group by ad.account

union
select
ad.account,
ad.lob,
SUM(ac.volume1) as Volume_PYT,
SUM(ac.volume2) as Volume_YTD,
SUM(ac.volume3) as Volume_CY

from accountdefinition ad

left join SUMMARY ac
on ad.account = ac.account
and ad.lob = ac.lob

where ad.lob not in ('AG','CH','EM','FC','MT','PA','PF')

group by ad.account,ad.lob
[/CODE]

Here are the results of my query
account LOB Volume_PYT Volume_YTD Volume_CY
Agway CO NULL NULL NULL
Agway Merchandise 5 5 100
Cerestar CO 1 1 100
Cerestar Merchandise 1 1 200

Here are the expected results
Since Agway doesn't have all 7 LOB's to make the logical group "Merchandise" (it is missing 'MT') we should see

Account LOB Volume_PYT Volume_YTD Volume_CY
Agway AG 5 5 100
Agway CH null null null
Agway EM null null null
Agway FC null null null
Agway PA null null null
Agway PF null null null
Agway CO null null null
Cerestar CO 1 1 100
Cerestar Merchandise 1 1 200

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 12:45:18

why Merchandise row didnt come for Agway? It has data against AG which is one mong LObs of merchandise

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jjw
Starting Member

9 Posts

Posted - 2012-02-07 : 13:05:30

quote:
Originally posted by visakh16

why Merchandise row didnt come for Agway? It has data against AG which is one mong LObs of merchandise

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

because Merchandise should only come up if Account has all 7 LOB's. In this case Agway only has 6 of the 7 LOB's that make up Merchandise, so they need to be listed individually.

This is really the problem I'm having and maybe the Group By / Union is the right approach. Because if any of the LOB's are in Merchandise then a Merchandise row appears and I only want a Merchandise row to appear if ALL 7 exist.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-02-07 : 13:20:26

[code]
WITH Merchandise
AS
(
SELECT
ad.account,
'Merchandise' AS LOB,
SUM(ac.volume1) as Volume_PYT,
SUM(ac.volume2) as Volume_YTD,
SUM(ac.volume3) as Volume_CY
FROM accountdefinition ad
LEFT JOIN SUMMARY ac
ON ad.account = ac.account
AND ad.lob = ac.lob
WHERE ad.lob IN ('AG','CH','EM','FC','MT','PA','PF')
GROUP BY ad.account
HAVING COUNT(DISTINCT ad.lob) = 7
)
SELECT
ad.account,
ad.lob,
SUM(ac.volume1) as Volume_PYT,
SUM(ac.volume2) as Volume_YTD,
SUM(ac.volume3) as Volume_CY
FROM accountdefinition ad
LEFT JOIN SUMMARY ac
ON ad.account = ac.account
AND ad.lob = ac.lob
WHERE NOT EXISTS
(
SELECT *
FROM Merchandise M
WHERE ad.account = M.account
AND ad.lob IN ('AG','CH','EM','FC','MT','PA','PF')
)
GROUP BY ad.account, ad.lob
UNION ALL
SELECT *
FROM Merchandise
ORDER BY account, lob;
[/code]

jjw
Starting Member

9 Posts

Posted - 2012-02-07 : 13:24:58

This query appears to identify the accounts that qualify for Merchandise grouping

select account,COUNT(distinct lob) from accountdefinition
where lob in ('AG','CH','EM','FC','MT','PA','PF')
group by account
having COUNT(distinct lob) = 7

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-02-07 : 13:28:47

This may perform better:


WITH Merchandise
AS
(
	SELECT account
	FROM accountdefinition
	WHERE lob IN ('AG','CH','EM','FC','MT','PA','PF')
	GROUP BY account
	HAVING COUNT(lob) = 7
)
SELECT
	ad.account,
	ad.lob,
	SUM(ac.volume1) as Volume_PYT,
	SUM(ac.volume2) as Volume_YTD,
	SUM(ac.volume3) as Volume_CY
FROM accountdefinition ad
	LEFT JOIN SUMMARY ac
		ON ad.account = ac.account
			AND ad.lob = ac.lob
WHERE NOT EXISTS
(
	SELECT *
	FROM Merchandise M
	WHERE ad.account = M.account
		AND ad.lob IN ('AG','CH','EM','FC','MT','PA','PF')
)
GROUP BY ad.account, ad.lob
UNION ALL
SELECT
	ad.account,
	'Merchandise' AS LOB,
	SUM(ac.volume1) as Volume_PYT,
	SUM(ac.volume2) as Volume_YTD,
	SUM(ac.volume3) as Volume_CY
FROM accountdefinition ad
	LEFT JOIN SUMMARY ac
		ON ad.account = ac.account
			AND ad.lob = ac.lob
WHERE EXISTS
(
	SELECT *
	FROM Merchandise M
	WHERE ad.account = M.account
            AND ad.lob IN ('AG','CH','EM','FC','MT','PA','PF')
)
GROUP BY ad.account
ORDER BY account, lob;

jjw
Starting Member

9 Posts

Posted - 2012-02-07 : 13:31:17

Awesome!!!! Thank-You very much!!!

quote:

Originally posted by Ifor


WITH Merchandise
AS
(
	SELECT
		ad.account,
		'Merchandise' AS LOB,
		SUM(ac.volume1) as Volume_PYT,
		SUM(ac.volume2) as Volume_YTD,
		SUM(ac.volume3) as Volume_CY
	FROM accountdefinition ad
		LEFT JOIN SUMMARY ac
			ON ad.account = ac.account
				AND ad.lob = ac.lob
	WHERE ad.lob IN ('AG','CH','EM','FC','MT','PA','PF')
	GROUP BY ad.account
	HAVING COUNT(DISTINCT ad.lob) = 7
)
SELECT
	ad.account,
	ad.lob,
	SUM(ac.volume1) as Volume_PYT,
	SUM(ac.volume2) as Volume_YTD,
	SUM(ac.volume3) as Volume_CY
FROM accountdefinition ad
	LEFT JOIN SUMMARY ac
		ON ad.account = ac.account
			AND ad.lob = ac.lob
WHERE NOT EXISTS
(
	SELECT *
	FROM Merchandise M
	WHERE ad.account = M.account
		AND ad.lob IN ('AG','CH','EM','FC','MT','PA','PF')
)
GROUP BY ad.account, ad.lob
UNION ALL
SELECT *
FROM Merchandise
ORDER BY account, lob;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources