Need help in writting query

prakashac · #1 (**permalink**) 02-21-10, 07:38

Hi,
Can any one please tell me, how to write query for the below example.

Standard TotalStudents Name
2 50 Raj
2 40 Sathish
3 35 Ganesh
4 50 Raj
5 40 Raj

The resulting query should display the total no of students and in another column the number of students whose name matches "Raj".

Like:

Toal Students Name
215 3

I can write query like this

select SUM(TotalStudents) as 'Total Students', (select SUM(TotalStudents) from student where name like 'Raj') as 'Name' from student.
Is there any other way to write it.

And also if i want to get the remaining students whose name don't matches with 'Raj' as another column.

Please reply soon..

Thanks in advance
Regards,
Prakash

r937 · #2 (**permalink**) 02-21-10, 08:31

Code:

SELECT SUM(TotalStudents) AS 'Total Students'
     , COUNT(CASE WHEN name = 'Raj'
                  THEN 'Todd'
                  ELSE NULL END) AS 'Total Rajs'
  FROM student

cavefish · #3 (**permalink**) 07-24-10, 20:09

Code:

SELECT  X.[Total Students]
           ,SUM(X.[TotalUser]) AS [Total Rajs]
FROM (SELECT SUM(TotalStudents) AS [Total Students]
           ,[TotalUser] = CASE WHEN [Name] = 'Raj' THEN 1 ELSE 0 END
          FROM student) X

r937 · #4 (**permalink**) 07-24-10, 22:12

dear cavefish, if you are going to resurrect a thread that has been sleeping quietly for five months, then you must at least make sure your post is reasonably free of errors

several expressions, e.g. X.[Total Students], are wrong because ANSI SQL (which is the forum you posted in) does not allow square brackets

[TotalUser] = is wrong because ANSI SQL does not allow assigning aliass in that manner

the CASE expression is wrong because it's missing the closing END keyword

Internet Services

Web Development

Digital Marketing

Consumer Tech