Results 1 to 7 of 7

Thread: [RESOLVED] mssql Just to make sure on having count

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,592

    Resolved [RESOLVED] mssql Just to make sure on having count

    Hi.

    ex1
    Code:
    select Idx from tblx
    group by Idx 
    having count(Idx )> 1
    That works

    ex2
    Code:
    select Idx,id2 from tblx
    group by Idx,id2
    having count(Idx)> 1
    That does not work (no rows returned)

    ex3
    Code:
    select Idx,id2,count(*) from tblx
    group by Idx,id2
    having count(*)> 1
    That works

    So am I do understand that ex2 will not work, unless I do something crazy like:


    Code:
    select s.Idx , t.Id2
    
    from
    
    (
    
    select Idx
    
    from tblx
    
    group by Idx
    
    having count(*) > 1
    
    )s
    
    join Idx t
    
    on s.Idx= t.Idx

    ?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,314

    Re: mssql Just to make sure on having count

    ex1 and ex3 imply that
    1) idx has duplicates
    2) id2 has duplicates

    as for ex2: Imagine a result like
    idx - id2
    1 - 1
    1 - 2
    1 - 3
    1 - 4
    and so on

    You are grouping by idx AND id2
    and the Having-Clause always goes on the GROUPED result
    So where do you have a COUNT(idx)>1 in there?

    i'm not sure, that ex3 actually returns the same results as ex1

    What are you actually trying to accomplish?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,592

    Re: mssql Just to make sure on having count

    It's more of a philosophical question.
    Focusing on ex2, what you are telling me is that even if a having count(Idx)> 1 that will actually not matter and the group by will take precedence?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,314

    Re: mssql Just to make sure on having count

    Quote Originally Posted by sapator View Post
    It's more of a philosophical question.
    Focusing on ex2, what you are telling me is that even if a having count(Idx)> 1 that will actually not matter and the group by will take precedence?
    Yes, GROUP BY takes precedence over Having.

    Think the HAVING-CLAUSE like a WHERE-Clause for a grouped resultset
    along the lines:
    Code:
    SELECT idx, MyCount FROM 
       (SELECT idx, COUNT(*) AS MyCount FROM MyTable GROUP BY idx)
    WHERE MyCount>1
    Last edited by Zvoni; Feb 12th, 2024 at 06:39 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,592

    Re: mssql Just to make sure on having count

    Thnx.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6

    Re: [RESOLVED] mssql Just to make sure on having count

    What is the significance of the GROUP BY clause taking precedence over HAVING in the context of the provided SQL queries?

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,314

    Re: [RESOLVED] mssql Just to make sure on having count

    It first GROUP's the records including aggregating, THEN applies the Filter in HAVING
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width