Aggregating records and displaying

Hi,

I have 2 tables:

Class - ClassID(int), ClassName(varchar)

Student - StudentID(int), ClassID, Age(smallint), Sex(nchar(1)), DateOfJoining(datetime)

I want to write a query that gives me the Top 10 Class which have maximum no. of student joining during the past 6 months. The query should include the following :

ClassName, Count(StudentID), Avg(Age), Avg(Sex)

From the avg(sex) i mean which was the sex which got enrolled more - ie. M or F. All I want is to find out that if in Class A, no of students enrolled were 50 and out of that 30 were Boys, then the end result of the query should be

Name Count Age Sex

ClassA 50 14 M(60%)

.....

ClassR 40 13 F(80%) ---- assuming out of 40, 32 are girls.

and so on....

How do I do this

Thanks.

Try:

select

[name],

cnt,

case

when sum_m > sum_f then 'M('

when sum_m < sum_f then 'F('

else 'MF('

end + str(

(

case

when sum_m >= sum_f then sum_m

when sum_m < sum_f then sum_f

end * 100.00) / nullif(cnt, 0), 5, 2) + ')'

from

(

select

c.name,

count(*) as cnt,

avg(s.age) as avg_age,

sum(case when s.sex = 'M' then 1 else 0 end) as sum_m,

sum(case when s.sex = 'F' then 1 else 0 end) as sum_f

from

dbo.class as c

inner join

dbo.student as s

on c.classid = s.classid

where

s.DateOfJoining >= convert(varchar(6), dateadd(month, -5, getdate()), 112) + '01'

) as t

AMB

Hi Arnie,

Could be, I did not test the statement. I am using function STR to convert the result number to string.

case

when sum_m > sum_f then 'M('

when sum_m < sum_f then 'F('

else 'MF('

end + str(

(

case

when sum_m >= sum_f then sum_m

when sum_m < sum_f then sum_f

end * 100.00) / nullif(cnt, 0), 5, 2) + ')'

AMB

Hi,

Thanks hunchback for the quick reply.

I keep getting the error :

Column 'dbo.class.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I tried adding the GroupBy to the inner select without much success.

Any clues!!

Thanks.

My fault, I forgot the "group by" and the "order by" clauses.

select

[name],

cnt,

case

when sum_m > sum_f then 'M('

when sum_m < sum_f then 'F('

else 'MF('

end + str(

(

case

when sum_m >= sum_f then sum_m

when sum_m < sum_f then sum_f

end * 100.00) / nullif(cnt, 0), 5, 2) + '%)'

from

(

select top 10

c.name,

count(*) as cnt,

avg(s.age) as avg_age,

sum(case when s.sex = 'M' then 1 else 0 end) as sum_m,

sum(case when s.sex = 'F' then 1 else 0 end) as sum_f

from

dbo.class as c

inner join

dbo.student as s

on c.classid = s.classid

where

s.DateOfJoining >= convert(varchar(6), dateadd(month, -5, getdate()), 112) + '01'

group by

c.name

order by

cnt DESC

) as t

AMB

I tried that..however put the groupby in the outer select. I need some sleep. hehe..anyways thanks.

It is definitely not better, but I did seem to get the correct type of answer with this:

Thanks. Both the queries are fine. I will use the profiler and select the best one.

Thanks again to all.