query help

Question

query help

yogi123

SSCrazy

Points: 2932
More actions
July 18, 2012 at 1:07 pm

#259096

hello i need help to develop query
create table #x1 (cStudentID varchar(12), sex char(1), RaceCode varchar(12), SchoolCode int)
insert into #x1 values ('003852962','M','White',160)
insert into #x1 values ('003929132','F','White',160)
insert into #x1 values ('003933873','M','Black',141)
insert into #x1 values ('003943658','M','Other',313)
insert into #x1 values ('004076427','F','Other',141)
insert into #x1 values ('004167511','M','Black',313)
insert into #x1 values ('004471652','F','Other',313)
insert into #x1 values ('004504232','F','Black',313)
insert into #x1 values ('004185951','M','Other',141)
insert into #x1 values ('004235754','F','Black',313)
insert into #x1 values ('004258053','M','White',160)
i need result should be display as
No of student by sex and race
No of student
Male
Balck 2
White2
Other2
Female
Balck2
White1
Other2

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

Can I ask why you want your results like that from a query? Are you going to be having the end users run this in SSMS or at the command line? I would suggest developing a report based of of a set of data instead of having your results returned in this fashion. However, if you need the results in this fashion, I can work on something that will get you those results.

Jared
CE - Microsoft

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Looks lke homework. Can you show us what you have done so far to solve your problem?

yogi123 SSCrazy Points: 2932 More actions · Answer 3

SQLKnowItAll (7/18/2012)
Can I ask why you want your results like that from a query? Are you going to be having the end users run this in SSMS or at the command line? I would suggest developing a report based of of a set of data instead of having your results returned in this fashion. However, if you need the results in this fashion, I can work on something that will get you those results.

I like your opinion,

If I develop report in SSRS then whati need to give expression for this, any idea?

i try =Count(IIF(IsNothing(Fields!RaceCode.Value),0,1))

but can't work

Jared SSC Guru Points: 61793 More actions · Answer 4

yogi123 (7/18/2012)
SQLKnowItAll (7/18/2012)
Can I ask why you want your results like that from a query? Are you going to be having the end users run this in SSMS or at the command line? I would suggest developing a report based of of a set of data instead of having your results returned in this fashion. However, if you need the results in this fashion, I can work on something that will get you those results.
I like your opinion,
If I develop report in SSRS then whati need to give expression for this, any idea?
i try =Count(IIF(IsNothing(Fields!RaceCode.Value),0,1))
but can't work

Just do this:

SELECT sex, RaceCode, COUNT(schoolCode) AS NumStudents

FROM #x1

GROUP BY sex, raceCode and then format your report to show this.

Jared
CE - Microsoft

yogi123 SSCrazy Points: 2932 More actions · Answer 5

yogi123

SSCrazy

Points: 2932

July 18, 2012 at 1:29 pm

#1514507

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

By the way, I have a query that will provide you the data, just not in the format you presented in your original post.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

This is what I wrote:

SELECT

CASE sex WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS Sex,

RaceCode,

COUNT(RaceCode) AS NumOfStudents

FROM

#x1

GROUP BY

sex,

RaceCode

ORDER BY

sex desc,

CASE RaceCode WHEN 'Other' THEN 1 ELSE 0 END, -- Sorts Other to the end of the list

RaceCode;

yogi123 SSCrazy Points: 2932 More actions · Answer 8

Lynn Pettis (7/18/2012)
This is what I wrote:
SELECT
CASE sex WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS Sex,
RaceCode,
COUNT(RaceCode) AS NumOfStudents
FROM
#x1
GROUP BY
sex,
RaceCode
ORDER BY
sex desc,
CASE RaceCode WHEN 'Other' THEN 1 ELSE 0 END, -- Sorts Other to the end of the list
RaceCode;

Thanks

Works Awesome

Appreciate