Group By Issue - 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)

Group By Issue

Author

Topic

jjw
Starting Member

9 Posts

Posted - 2012-01-27 : 17:54:47

So I'm trying to write a query to be utilized in a report to identify some summations by category codes. The requirement that is throwing me off a little bit is that if 7 of the 12 category codes exist to group them together as "Products". But the other 5 will always be individual. In addition, if you didn't have all 7 (that make up "Products"), they would just be grouped by category.

For example:
select
customerId,
categorycode,
sum(metrics) as Revenue

from account
where accountId = @AccountId
group by customerid,categorycode

This will produce a line for each category a current customer has.
CustId CategoryCode Revenue
34324 Red $750
34324 Blue $1,000
34324 Yellow $500
34324 Green $1200

So lets tone it down for the example. Let's say that categories Red + Blue +Green = "Products" and Yellow stays by itself. So I would want to see the following when that combination exists:
CustId CategoryCode Revenue
34324 Products $1950
34324 Yellow $500

What do you guys think?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-27 : 18:03:05

the best way for this is to create a mapping table and then link that to current query. it will have columns customerid,parentcategory with value as Products and child categories as current codes. for individual ones just add a mapping to itself. Then group them based on parent category and then take count(distinct child) to see if its equal to total count. then use it for grouping.

so query will be like


select 
m.customerId,
case when count(distinct m.childcategory) = count(distinct a.categorycode) then m.parentcategory else a.categorycode end,
sum(metrics) as Revenue

from mappingtable m
left join account a
on a.categorycode = m.childcategory
and a.customerId = m.customerId
and a.accountId = @AccountId
group by m.customerid,case when count(distinct m.childcategory) = count(distinct a.categorycode) then m.parentcategory else a.categorycode end

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-27 : 18:06:49

see..you give us a very simplified request..we KNOW that what you want is more complicated..people will then fall over themselves to give you an answer based on what you poasted, you will come back and say no..I really need...so post what you really need

Do you really have red+yellow+blue and then everything else?

No?

I didn't think so

Read the hint link in my sig

in any case

select BLAH, SUM(something)
FROM table
WHERE col IN ('red','yellow','blue')
GROUP BY BLAH
UNION
select BLAH, SUM(something)
FROM table
WHERE col NOT IN ('red','yellow','blue')
GROUP BY BLAH

HTH

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jjw
Starting Member

9 Posts

Posted - 2012-01-28 : 14:58:47

quote:
Originally posted by X002548

see..you give us a very simplified request..we KNOW that what you want is more complicated..people will then fall over themselves to give you an answer based on what you poasted, you will come back and say no..I really need...so post what you really need

Do you really have red+yellow+blue and then everything else?

No?

I didn't think so

Read the hint link in my sig

in any case

select BLAH, SUM(something)
FROM table
WHERE col IN ('red','yellow','blue')
GROUP BY BLAH
UNION
select BLAH, SUM(something)
FROM table
WHERE col NOT IN ('red','yellow','blue')
GROUP BY BLAH

This doesn't seem to identify the conditions where the account would have red & yellow but not blue.

jjw
Starting Member

9 Posts

Posted - 2012-01-30 : 23:54:04

I tried this solution and am getting an error that you cannot group by "Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause."

Here is the query I used to build the mapping table
[Code]
select
ad.customerid,
ad.customeridname,
case when ad.lob in ('AG','CH','EM','FC','MT','PA','PF') then 'Merchandise' else ad.lob end AS Parent,
ad.lob as LOB

into #test

from Filteredaccountdefinition ad

left join edw.AG_CRM_CUSTOMER_LOC_SUMMARY ac
on left(ad.customeridname,4) = left(ac.patron_c,4)
and ad.lob = LEFT(ac.lob_c,2)

where ad.accountid = @accountID
and ad.rolluptypename = 'main'

group by ad.customerid,ad.customeridname,ad.csx_lob
[/CODE]

Here is the query I wrote to join the mapping table that produced the error. Any ideas?

[CODE]

select m.customeridname,
case when COUNT(distinct m.LOB) = COUNT(distinct ad.lob) then m.Parent else ad.lob end as LOB,
SUM(ac.PY_TOT_IB_UNITS_Q) + SUM(ac.PY_TOT_OB_UNITS_Q) as Volume_PYT,
SUM(ac.PY_TOT_IB_REVENUE_A) + SUM(ac.PY_TOT_OB_REVENUE_A) as REV_PYT,
SUM(ac.PY_TOT_IB_NET_TONS_DECIMAL_Q) + SUM(ac.PY_TOT_OB_NET_TONS_DECIMAL_Q) as TONS_PYT,
SUM(ac.CY_YTD_IB_UNITS_Q) + SUM(ac.CY_YTD_OB_UNITS_Q) as Volume_YTD,
SUM(ac.CY_YTD_IB_REVENUE_A) + SUM(ac.CY_YTD_OB_REVENUE_A) as REV_YTD,
SUM(ac.cy_ytd_ib_net_tons_decimal_q) + SUM(ac.cy_ytd_ob_net_tons_decimal_q) as TONS_YTD

from #test m
left join Filteredaccountdefinition ad
on ad.lob = m.LOB
and ad.customerid = m.csx_customerid
left join edw.AG_CRM_CUSTOMER_LOC_SUMMARY ac
on left(ad.customeridname,4) = left(ac.patron_c,4)
and ad.lob = LEFT(ac.lob_c,2)

group by m.customeridname,case when COUNT(distinct m.LOB) = COUNT(distinct ad.lob) then m.Parent else ad.lob end

quote:
Originally posted by visakh16

the best way for this is to create a mapping table and then link that to current query. it will have columns customerid,parentcategory with value as Products and child categories as current codes. for individual ones just add a mapping to itself. Then group them based on parent category and then take count(distinct child) to see if its equal to total count. then use it for grouping.

so query will be like

select
m.customerId,
case when count(distinct m.childcategory) = count(distinct a.categorycode) then m.parentcategory else a.categorycode end,
sum(metrics) as Revenue

from mappingtable m
left join account a
on a.categorycode = m.childcategory
and a.customerId = m.customerId
and a.accountId = @AccountId
group by m.customerid,case when count(distinct m.childcategory) = count(distinct a.categorycode) then m.parentcategory else a.categorycode end

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources