Sum + Case + Group By Problem

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

May 25th, 2004, 04:46 PM

Deacon Blues

Registered User

Join Date: May 2004

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

Sum + Case + Group By Problem

I am having a problem with a query. I am getting and extra line from my query that I should. The query is:

select Product_Grouping =
case
when Product_Grouping = 3 or Product_Grouping = 4 then 5 else product_grouping

end
, sum(TY_revenue), sum(LY_Revenue)
from Products
group by product_grouping

My results should be as follows:

Product_Grouping TY_Revenue LY_Revenue
1 34,673,635 34,450,574
2 25,714,032 28,569,706
5 29,052,227 28,939,060

But what I get is:
Product_Grouping TY_Revenue LY_Revenue
1 34673635.0000 34450574.0000
2 25714032.0000 28569706.0000
5 20058886.0000 21018703.0000
5 8993341.0000 7920357.0000

When I add the numbers up, they do add up to the correct number but I should not have the extra line of 5. Any ideas as to what is causing this?

Thanks,

Scott

May 25th, 2004, 09:11 PM

Username=NULL

Contributing User

Join Date: Apr 2004

Location: TX

Posts: 249

Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5

The group by isn't gonna refer to the 'product_grouping' that was manipulated by the CASE, it's gonna look @ the orig value in the table. I believe (but may be wrong) that you're getting 4 rows instead 3 b/c of that principle...you can keep what you have, and just wrap another query around it, and I think you'll get what you're looking for...be warned though, it will seem very redundant...someone else will probably chime in w/a better solution though.

Code:

select	product_grouping,
	sum(ty_revenue),
	sum(ly_revenue)
from	(
	 select	CASE
	 	  When Product_grouping = 3 or Product_grouping = 4 Then 5
		  Else Product_grouping	
		END [Product_grouping],
		sum(TY_revenue) as TY_revenue,
		sum(LY_Revenue) as LY_revenue
	 from	Products
	 group	by product_grouping
	) as A 
group	by product_grouping

Last edited by Username=NULL : May 25th, 2004 at 09:18 PM.

May 26th, 2004, 06:23 AM

r937

SQL Consultant

Dev Shed God 25th Plane (17000 - 17499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,343

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 5 m 22 sec
Reputation Power: 891

i think null has identified the problem, and the solution is to give the case expression column a different alias --

Code:

select case when Product_Grouping = 3 
              or Product_Grouping = 4 
            then 5 
            else product_grouping
        end
          as any_other_name
     , sum(TY_revenue) as sumTY
     , sum(LY_Revenue) as sumLY
  from Products
group 
    by any_other_name

__________________
r937.com | rudy.ca

May 26th, 2004, 08:52 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,766

Time spent in forums: 2 Weeks 4 Days 6 h 24 m 8 sec
Reputation Power: 37

That is not allowed by SQL server.

Quote:

group_by_expression

Is an expression on which grouping is performed. group_by_expression is also known as a grouping column. group_by expression can be a column or a nonaggregate expression that references a column. A column alias that is defined in the select list cannot be used to specify a grouping column.

May 26th, 2004, 09:28 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,343

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 5 m 22 sec
Reputation Power: 891

thanks, swampboogie

it's hard to remember all the nuances of so many database systems

so the GROUP BY would have to use the CASE expression too, then

May 26th, 2004, 10:40 AM

Deacon Blues

Registered User

Join Date: May 2004

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

Got It To Work

Thanks to all of you that replied to my question. You were of great help with this problem. This is the query that I got to work.

Thanks,

Scott

Code:

select	Product_Grouping,
	sum(ty_revenue) [TY_Revenue],
	sum(ly_revenue) [LY_Revenue]
from (select CASE
	When Product_grouping = 3 or Product_grouping = 4 Then 5
	Else Product_grouping	
	END [Product_grouping],
	sum(TY_revenue) as TY_revenue,
	sum(LY_Revenue) as LY_revenue
	from	Products
	group	by product_grouping
	) as A 
group by product_grouping

June 16th, 2004, 08:42 PM

abibaby

Registered User

Join Date: Jun 2004

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

DECODE is faster than Case

Use

DECODE(Product_grouping, 3, 5, 4, 5, Product_grouping)

Instead of

CASE
When Product_grouping = 3 or Product_grouping = 4 Then 5
Else Product_grouping
END [Product_grouping],

June 16th, 2004, 08:48 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,343

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 5 m 22 sec
Reputation Power: 891

you're prolly thinking of oracle

this is mysql

in mysql, the DECODE function is used for encryption:

Quote:

DECODE(crypt_str,pass_str) Descrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE()

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: