Select & Count - 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

General SQL Server Forums

New to SQL Server Programming

Select & Count

Author

Topic

osullivj
Starting Member

4 Posts

Posted - 2011-05-30 : 16:49:14

Ok Guys..I am struggling with this but you guys should be able to help

I have a table like this
ID, AREA, SUB_AREA
1, x, s
2, x, s
3, x, t
4, y, y

and would like to get the following answer, the number of sub areas per area

AREA, COUNTSUBAREAS
x, 2
y, 1

You help is appreciated

Thanks,
J

As our society gets more complex and our people get more complacent, the role of the jester is more vital than ever before. Please stop sitting around. We need you to make a ruckus.
Seth Godin - Linchpin

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-05-30 : 17:32:57

try this:


select area, count(area) as count_subarea
from table_like_this
group by area

and result will be:
AREA, COUNTSUBAREAS
x, 3
y, 1

best

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-30 : 17:47:09

Or this, a slight tweak to slimt

select area, count(distinct area) as count_subarea
from table_like_this
group by area

Jim

Everyday I learn something that somebody else already knew

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-30 : 19:57:28

should it be counting distinct sub_area ?

KH
[spoiler]Time is always against us[/spoiler]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-30 : 20:02:14

Oops! Yes, it should be sub_area.

Jim

Everyday I learn something that somebody else already knew

osullivj
Starting Member

4 Posts

Posted - 2011-05-31 : 09:30:42