defining columns

[humvie]

I can't seem to get my head wrapped around this one ...

example: assuming table has 25 names with their ages
I simply want a count for each age group
between 1-10 as 10
between 11-20 as 20
between 21-30 as 30

So essentially the result with return Three columns with a count for each group.

10 20 30
-------------
11 5 9


I tried variations of ... SELECT COUNT(*) FROM Persons WHERE Age>20

Any help would be appreciated.
Thank you

n.b. the data may not make sense and that's ok, I'm only trying to understand how to group things

 

[bborissov]

SELECT SUM(CASE WHEN AGE < 11 THEN 1 ELSE 0 END) AS AGE10,
       SUM(CASE WHEN AGE > 10 AND Age < 21 THEN 1 ELSE 0 END) AS AGE20,
       SUM(CASE WHEN AGE > 20 AND Age < 31 THEN 1 ELSE 0 END) AS AGE30
FROM MyTable

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[sweth]

One way is
select
Group10 = (select count(*) from person where age between 0 and 10)
,Group20 = (select count(*) from person where age between 10 and 20)
,Group30 = (select count(*) from person where age between 20 and 30)

Does it solve your problem.

Let me know from group is there any other way

Thanks and Regards
Siva.
Vendor,Microsoft India,Hyderabad

 

[Catadmin]

The above two solutions work great if you're just looking for a count as you said in your post. But you also mentioned learning about grouping in general and there are days when a Case or simple SELECT statement just doesn't do it.

Look up the following terms in Books Online: Group By and Having.

Here's a sample bit of code when you might need to use Group By and Having (such as if you have multiples of the same/similar record in a table):

Select Name, Age
from tblBirthdays
Group By Name, Age
Having Count(Name) > 2

I hope this helps you out.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"