Including SUM, WITH (case) - 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)

Including SUM, WITH (case)

Author

Topic

fwdtech
Starting Member

11 Posts

Posted - 2013-08-30 : 14:49:16

I have one query that returns a set of age ranges along with the count in that range via:

WITH x AS ( select ... )
SELECT CASE
WHEN x.age BETWEEN 30 AND 39 THEN '30-39'
[ect through 99+]
AS [Age], COUNT(*) AS [Count]
FROM x
GROUP BY
CASE WHEN
WHEN x.age BETWEEN 30 AND 39 THEN '30-39'
[ect through 99+]
END;

So I get back 30-39 | 15, 40-49 122 (ect...)

And another report that generates the average income by client classification via:

select x.cl, count(*) as records, sum(w.a, w.b, w.c)
from clients c
inner join wages w on [bla bla]
inner join classifications x on [bla bla]
group by x.cindex, x.cl

Which gives me "aaa,7,51484.00 | btr,22,85616.00" (ect).

Now I'm tasked with generating the age breakdown query combined with the average income by class so I would print out "30-39 = 41566" - "40-49 61523" ect.

I tried adding the sum() to the first report but I keep getting the 'group by' error in the query. It's 3 tables, one with wages as 7 separate fields, one with the clients class (int) and one with the classifications themselves (int+description).

Can I do the select x with ( select... ) query as I did in the age report and combine it with SUM of values from another query? As of now, I wrote a routine to perform the calculations in .net but I'm curious if I can move this back to SQL.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-30 : 16:40:14

[code]SELECT CAST(Number / 10 AS VARCHAR(3)) + '-' + CAST(Number / 10 + 9 AS VARCHAR(3)),
COUNT(*)
FROM master.dbo.spt_values
WHERE Type = 'P'
GROUP BY Number / 10;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-09-03 : 12:47:17

Here's an example on how to chain CTEs and combine them:

WITH cteNumbers
AS
(
SELECT		Number
FROM		master.dbo.spt_values
WHERE		Type = 'P'
)
--SELECT * FROM cteNumbers
/* ----------------------------------
Number
0
1
2
3
4
...
1022
1023
1024
...
*/ ----------------------------------
, cteAgeRange
AS
(
SELECT		Number*10 AS RangeBegin,
			Number*10+9 AS RangeEnd,
			CAST(Number*10 AS VARCHAR(3)) + '-' + CAST(Number*10 + 9 AS VARCHAR(3)) as AgeRange
FROM		cteNumbers AS n
WHERE		n.Number <= 13 -- Max Age of 139
)
 -- SELECT * FROM cteAgeRange
/* ----------------------------------
RangeBegin	RangeEnd	AgeRange
0	9	0-9
10	19	10-19
20	29	20-29
30	39	30-39
40	49	40-49
50	59	50-59
60	69	60-69
70	79	70-79
80	89	80-89
90	99	90-99
100	109	100-109
110	119	110-119
120	129	120-129
130	139	130-139
*/ ----------------------------------
SELECT		AgeRange,
			COUNT(*) AS TotalNumbersInRange
FROM		cteAgeRange AS r
JOIN		cteNumbers as n
ON			n.Number BETWEEN r.RangeBegin AND r.RangeEnd
GROUP BY	AgeRange,RangeBegin
ORDER BY	RangeBegin
/* ----------------------------------
AgeRange	TotalNumbersInRange
0-9	10
10-19	10
20-29	10
30-39	10
40-49	10
50-59	10
60-69	10
70-79	10
80-89	10
90-99	10
100-109	10
110-119	10
120-129	10
130-139	10
*/ ----------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources