SUM topic -- being thick - 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 2000 Forums

SQL Server Development (2000)

SUM topic -- being thick

Author

Topic

uberbloke
Yak Posting Veteran

67 Posts

Posted - 2002-01-29 : 11:22:26

I need to calculate two figures, total number of people in each area above a certain level and total number below, and I can't for the life of me work out an easy way of doing it without temp tables and the like ...

there must be a sql way!

DATA
Person, Area
a 1
b 1
c 2
d 2
e 3
f 3
g 1
h 1
i 4
j 5
k 2

OK, so if the divider is 3 then I want to see the following result
above 3 = 7 (area 1 has 4 people, area 2 has 3)
below 3 = 4 (area 3 has 1 person, area 3 has 2 and area 5 has 1)

Having a thick day, can some one a) help and b) explain how they come to the solution so I know where to start next time

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-29 : 11:35:13


SELECT COUNT(CASE WHEN Area < @divider THEN 1 END) AS Above,
  COUNT(CASE WHEN Area >= @divider THEN 1 END) AS Below
FROM DATA

or if you wanted it the other way round


SELECT Position, COUNT(*)
FROM (SELECT CASE WHEN Area < @divider THEN 'Above' ELSE 'Below' END AS Position) a
GROUP BY Position

Edited by - Arnold Fribble on 01/29/2002 11:38:08

uberbloke
Yak Posting Veteran

67 Posts

Posted - 2002-01-29 : 11:35:56

<smug mode on>
May have stopped being thick now!
<smug mode off>

Here is some sample data and the select statement, pls respond if I have made any glaring errors (hopefully you wont puncture my current feeling smugness)

create table #temp(
area int,
person char(1)
)

insert into #temp values (1, 'a')
insert into #temp values (1, 'b')
insert into #temp values (2, 'c')
insert into #temp values (2, 'd')
insert into #temp values (2, 'e')
insert into #temp values (2, 'f')
insert into #temp values (3, 'g')
insert into #temp values (3, 'h')
insert into #temp values (3, 'i')
insert into #temp values (3, 'j')
insert into #temp values (3, 'k')
insert into #temp values (3, 'l')

select * from #temp

select sum(number)
from
(select count(*) as number, area from #temp
group by area) as results
where number > 4

drop table #temp

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-29 : 11:46:27

Surely that's the total number of people in areas with more than 4 people?
Was that what you meant? Ah, your test data and result could have been either! I thought level and area were synonymous in your question. Oops.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources