Conditionally combine values in a return set? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Conditionally combine values in a return set?

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2009-01-08 : 14:47:44

I've got a little query that will show us what our sales total was per market segment in a given period, but recently the decision was made to rename one of the segments from 'AUTO' to 'TRANS'.

For the sake of clarity, I want to combine totals in segment 'AUTO' and 'TRANS' so that they appear under a single heading.

This is the query:

DECLARE @dtFromDate as DateTime, @dtToDate as DateTime
SET @dtFromDate = '2008-08-01 00:00:00.000'
SET @dtToDate = '2008-08-31 23:59:59.999'

SELECT  
	SUM(aritem.ftotprice) AS TI, 
	CASE somast.fusercode 
		WHEN 'AUTO' THEN 'TRANS'
		ELSE somast.fusercode
	END as FU
FROM 
	aritem join armast on aritem.fcinvoice = armast.fcinvoice 
	LEFT JOIN somast on LEFT(aritem.fsokey, 6) = somast.fsono 
WHERE 
	finvdate BETWEEN @dtFromDate and @dtToDate 
	AND aritem.fpartno NOT IN ('SHIPPING') 
	AND aritem.fcinvoice NOT LIKE ('CM%') 
	AND aritem.fcinvoice NOT LIKE ('PM%') 
	AND aritem.fsokey NOT IN ('') 
	AND armast.fcstatus NOT IN ('V')
	AND somast.fusercode IN ('ARCH', 'AUTO', 'INDS', 'PIPE', 'TRANS') 
GROUP BY
	somast.fusercode
ORDER BY
	somast.fusercode

Right now it returns


123.45	ARCH
123.45	TRANS
123.45	INDS
123.45	PIPE
123.45	TRANS

where I'd prefer


123.45	ARCH
123.45	INDS
123.45	PIPE
246.90	TRANS

The sort order isn't so important, as I can do that after its bound to a DataGrid, I'm more looking for a way to combine the totals of seemingly unrelated data. How big of a problem is that going to be (I have tons more queries that need the same treatment).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-08 : 15:06:39

Change the group by to be exactly the same as what you have in the select:

GROUP BY
CASE somast.fusercode
WHEN 'AUTO' THEN 'TRANS'
ELSE somast.fusercode
END

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-08 : 15:07:32

group it by

CASE somast.fusercode 
		WHEN 'AUTO' THEN 'TRANS'
		ELSE somast.fusercode
	END as FU

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2009-01-08 : 15:14:53

Fantastic, that did the trick; thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources