Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2012
    Posts
    10

    Question Unanswered: Urgent - Query using Group by ??? Please help

    Hi All,
    I need SQL for the below scenario -
    one employee may have multiple records with either 0,1 or 2 for the coulmn A. I need to Select the list of employees who do not have value 1 for column A.

    Sample table :

    E_NO A
    01 1
    02 0
    03 0
    01 0
    01 2
    02 1
    02 0
    02 0
    03 0

    I think we need to use group by clause to get the list. As I'm new to SQL, It would be great if any one of you can help me on this!!

    Thanks in advance!
    Last edited by gbala; 03-19-12 at 00:39.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT e_no
      FROM daTable
    GROUP
        BY e_no
    HAVING COUNT(CASE WHEN a = 1 THEN 'uh oh' ELSE NULL END) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2012
    Posts
    10
    Hi, Thanks for the quick reply.. Can you please help me figure out how i can ensure that this query gives correct number of records .. because this table has more than 75k records and only very few employees are not having the value 1.

    Root cause :
    The entries for this table is populated in such a way that each employee should have one record with value 1. Becuase of some issue, some of the employee records not populated with value 1. So I'm trying to find out employees who do not have a record with value 1 in the table. Your query is giving me a list of employees, but i want to make sure it is giving the correct number. Can you please help me with this? Thank you so much!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one way to make sure it is giving the correct number is to create a copy of the table, load it with a small number of rows, covering all possibilities, and then count the employees manually
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you please help me figure out how i can ensure that this query gives correct number of records
    Use the PK's you got in the result set and JOIN them back with the table. Those PK's are from those groups without a single a = 1. So when you add WHERE a = 1 to this group, you should get zero (0) records.
    Code:
    SELECT *
    FROM daTable
    	INNER JOIN (SELECT e_no
    		    FROM daTable
    		    GROUP BY e_no
    		    HAVING COUNT(CASE WHEN a = 1 THEN 'uh oh' ELSE NULL END) = 0
    		   ) as T ON
    		DaTable.e_no = T.e_no
    WHERE DaTable.a = 1
    And do what r937 suggested: create a temporary table with some sample data in to verify your query.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Mar 2012
    Posts
    10
    Hi All , thanks for the replies. I need another help for this same issue. I need to retrieve entire row for the employee who do not have value 1 for column A based on min(creatdate). It may be somethin like this ???

    SELECT *
    FROM daTable
    where e_no in (SELECT e_no , min(createdate)
    FROM daTable
    GROUP BY e_no
    HAVING COUNT(CASE WHEN a = 1 THEN 'uh oh' ELSE NULL END) = 0
    )

    I know above query is incorrect. Please help me with this issue! Thanks,

  7. #7
    Join Date
    Mar 2012
    Posts
    10
    Hi, here is the query for my scenario:

    SELECT *
    FROM daTable
    INNER JOIN (SELECT e_no, min(creatdate) date
    FROM daTable
    GROUP BY e_no
    HAVING COUNT(CASE WHEN a = 1 THEN 'uh oh' ELSE NULL END) = 0
    ) T ON
    DaTable.e_no = T.e_no and t.date = DaTable.creatdate

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The proof of the pudding is in the eating. Why don't you test it?

    I think your query is OK, but i'm only 100% sure of this part.
    Code:
    HAVING COUNT(CASE WHEN a = 1 THEN 'uh oh' ELSE NULL END) = 0
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Wim View Post
    ...but i'm only 100% sure of this part.
    thank you
    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
  •