Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2010
    Posts
    1

    Unanswered: Need help in writting query

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT SUM(TotalStudents) AS 'Total Students'
         , COUNT(CASE WHEN name = 'Raj'
                      THEN 'Todd'
                      ELSE NULL END) AS 'Total Rajs'
      FROM student
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Posts
    1
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •