Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Smile Unanswered: fetching data using "group by" clause problem

    i have a table named by "student_details".

    student_roll | marks | status
    1 88 1
    1 90 1
    2 67 1
    2 66 2
    3 56 1
    3 45 1
    3 77 1



    student_roll is a foreign key and hence primary in othr table.


    ques - i want a result set like

    student_roll | marks | status
    1 88 1
    1 90 1
    3 56 1
    3 45 1
    3 77 1



    condition :- i want to select values from a table group by student_roll and make sure that same status for a particular student_roll.

    student_roll 1 has status 1 for both the rows.

    student_roll 3 has status 1 for all the rows and hence accepted.

    student_roll 2 has different status and hence neglected.


    Kindly help me out.


    Thanks guys

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Check out "where not exist"

  3. #3
    Join Date
    Sep 2010
    Posts
    153

    Smile Thanks a lot but

    can u write q eury for me?

  4. #4
    Join Date
    Sep 2010
    Posts
    153
    kindly help me, i need the solution urgently Plz spend your time if possible.


    Thanks a lot

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.student_roll
         , t.marks
         , t.status
      FROM ( SELECT student_roll
               FROM student_details
             GROUP
                 BY student_roll
             HAVING COUNT(*) =
                    COUNT(CASE WHEN status = 1
                               THEN 'whoopee' END )
           ) AS x
    INNER
      JOIN student_details AS t
        ON t.student_roll = x.student_roll
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2010
    Posts
    153

    Smile @r97

    Thanks a lot you are superb but one more problem in it

    I guess you are referring only to status as 1 (status = 1)..

    suppose, for a particular student_roll we have status as 2

    student_roll | marks | status
    12 77 2
    12 78 2
    12 80 2


    if this is the scanario, we have to include these details also because student_roll has same status values.


    Kindly help me out

    Thank you

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sunny_007 View Post
    I guess you are referring only to status as 1 (status = 1)..
    that's more or less the impression i got from your original post

    i'm not going to hand you the answer, though, i'm going to ask that you give it a try for yourself

    first, make sure you understand how my solution works for status=1 -- it's done by counting the rows in each group and comparing this to the count of status 1 rows

    then, try to come up with a strategy that, instead of counting the status 1 rows, allows you to count something else

    hint: you're looking for groups which have only one distinct status, regardless of what status it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2010
    Posts
    153

    Smile i could'nt do it :(

    Thanks a lot for the solution but i guess i am not good enough to do it . I am still trying but as it is very urgent, can you write a query for me this time? i will be thankful to you.



Posting Permissions

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