Custom group by possibility - 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 2005 Forums

Transact-SQL (2005)

Custom group by possibility

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-02-07 : 04:22:49

I am currently pulling a report that shows people's ages within my organisation. Currently I am grouping the data by age, so my results look something like this:


Age  |  Total
-------------
21        3
25        1
29        4
34        2
65        1

Rather than group by age, is it possible to force the Age column to show all values (i.e. 16 --> 75yrs (inclusive), then show the number of people of that age alongside it?

That would provide much more powerful reporting because I could then see the distribution of ages using fixed boundaries.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 04:34:04

[code]SELECT v.Number AS Age,
COUNT(e.Age) AS Total
FROM master..spt_values AS v
LEFT JOIN Employees AS e ON e.Age = v.Number
WHERE v.Type = 'p'
AND v.Number BETWEEN 16 AND 75
GROUP BY v.Number
ORDER BY v.Number[/code]

E 12°55'05.25"
N 56°04'39.16"

R
Constraint Violating Yak Guru

328 Posts

Posted - 2008-02-07 : 06:57:34

Hi Peso
That works great!

Can you please just explain to me what spt_values is, as I've not seen that before?

Thanks.

T-SQL seemed so easy in the training videos...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-07 : 07:07:42

It is system table that can be used as a TALLY table.
For SQL Server 2000, the master..spt_values WHERE Type = 'p' gives you numbers between 0 and 255.
For SQL Server 2005, the master..spt_values WHERE Type = 'p' gives you numbers between 0 and 2047.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources