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 2000 Forums

Transact-SQL (2000)

case

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-01-18 : 10:38:21

Hi,

I need to display the amount of students that are in a school by the country of origin.
I thought of using cases but there are over 90 countries so it is not logical to do the following:
if (@Paramreport =1) or (@Paramreport =0)
begin
select
count(CASE country WHEN '420' THEN 1 END) as S.Africa,
count (case country when '421' then 1 end) as USA,
ETC....
sum (1) as Total
from students
END

I thought of doing something like this:
select count ( *) as total,countryname
from student
group by countryname

but that gives me the results as follows:
S.Africa 23
America 12

and I need the results as follows:
S.Africa America
23 12

Thanks in advance for the help.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-18 : 11:16:33

you did not show your whole SQL expression so we can't tell you what's wrong with it. chances are, to have too many columns in your GROUP BY clause (from what you have posted, there should probably be none)

- Jeff

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-01-18 : 11:48:21

quote:
Originally posted by collie

Hi,

I need to display the amount of students that are in a school by the country of origin.
I thought of using cases but there are over 90 countries so it is not logical to do the following:
if (@Paramreport =1) or (@Paramreport =0)
begin
select
count(CASE country WHEN '420' THEN 1 END) as S.Africa,
count (case country when '421' then 1 end) as USA,
ETC....
sum (1) as Total
from students
END

I thought of doing something like this:
select count ( *) as total,countryname
from student
group by countryname

but that gives me the results as follows:
S.Africa 23
America 12

and I need the results as follows:
S.Africa America
23 12

Thanks in advance for the help.

It's hard for us to understand u but I think in your code:
begin
select
count(CASE country WHEN '420' THEN 1 END) as S.Africa,
count (case country when '421' then 1 end) as USA,
ETC....
You should replace count with sum like:
sum(CASE country WHEN '420' THEN 1 ELSE 0 END) as S.Africa,

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-01-18 : 12:50:43

Hi,

Thanks for the responses.

Let me try explain myself :-)

I have to make a sp that will display results (one next to another) as follows:
America Africa
12 9

and not one under the other as follows:
America 12
Africa 9.

I have over 90 countries in the db.
I tried the following query
select count ( *) as total,countryname
from student
group by countryname

but it displays the results one under the other

America 12
Africa 9

and i need the results in one row (name of country in one row and under each country the total for that country).
I know that if i use case I will get the desired result but i don't want to have over 90 cases as follows:
select
(case countryname when 420 then 1 end) as America,
(case countryname when 421 then 1 end) as Africa
and so forth.

I want to write a query that will give me the names of the country in one row with each country's total in a row below the country but I don't know how to do it w/o having cases.

Hope it's clearer now

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-18 : 13:12:39

[code]
DECLARE @Result VARCHAR(8000)

SELECT @Result = COALESCE(@Result + ', ','') + countryname + ': ' + CONVERT(varchar(15),total)
FROM (
SELECT countryname, COUNT(*) as total
FROM student
GROUP BY countryname
) AS XXX

SELECT @Result

[/code]

Brett

8-)

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-01-18 : 14:40:31

Hi,

Nice query :-)
The only problem is that it gives me the result as follows:
SAfrica: 2, America : 3, England: 2

and not the way which I need which is:
SAfrica America England
2 3 2

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-18 : 14:47:35

how are you displaying these results? this is often very easy to do in the presentation layer, as opposed to in SQL.

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-18 : 14:52:15

Brett, just gave you the solution.
You just have to work on it.

DECLARE @Result1 VARCHAR(8000), @Result2 VARCHAR(8000)
.....
SELECT @Result1 UNION SELECT @Result2

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-18 : 16:30:45

Looks like you want labels..why not just use the application layer to parse out the data? I could give you a solution for what you want, but it separates the data elements. There's nothing to tie the rows together physically and you could have problems.

BUT! You could do it.

Heck I would tell my developer to take all the rows and build it in the presentation layer.

What's the problem here?

Brett

8-)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-19 : 02:23:21

It seems something like this




DECLARE @Result VARCHAR(8000)
Declare @Tot varchar(200)
Declare @Student table(Countryname nvarchar(100))
insert into @student values ('SAfrica')
insert into @student values ('SAfrica')
insert into @student values ('America')
insert into @student values ('America')
insert into @student values ('SAfrica')

SELECT @Result = COALESCE(@Result + '  ' + countryname,countryname) ,
       @tot= COALESCE(@Tot + '          '+ CONVERT(varchar(15),total),CONVERT(varchar(15),total))
  FROM (
	  SELECT countryname, COUNT(*) as total
	    FROM @student
	GROUP BY countryname
	) AS XXX

SELECT @Result as Result union
select @tot order by result desc

The result is

America SAfrica
2 3

Madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources