CODEWITHSUNDEEP
sql-servert-sqlhaving
user5785372
user5785372

Reputation: 13

Group by and Having Clause that uses multiple columns

The AND condition in the below Having clause behaves like it filters the row even if one of the conditions is met when its supposed to be logical AND behavior.

with cte as 
( select 'A' as name , 10 as Classes , 11 as Fees union all
 select 'A' as name , 10 as Classes , 10 as Fees union all
  select 'A' as name , 10 as Classes , 10 as Fees union all
   select 'A' as name , 10 as Classes , 10 as Fees union all
    select 'A' as name , 10 as Classes , 10 as Fees union all
     select 'B' as name , 0 as Classes , 10 as Fees union all
      select 'B' as name , 0 as Classes , 10 as Fees union all
       select 'B' as name , 1 as Classes , 10 as Fees union all
        select 'B' as name , -10 as Classes , 10 as Fees union all
         select 'B' as name , 10 as Classes , 11 as Fees 
       )
       -- A has 50 rooms and 51 $ -- so it should not be returned
       -- B has 1 room and 51 $ and should be fetched
        -- why would the AND in the having clause  behave like a row level filter on only the indivigual column 
        -- instead of the combination
        -- The weird thing is the logic behaves as expected when using = but fails when using <>
       select name , sum(Classes) , sUM(Fees)
       from cte 
       group by name 
       having ( SUM(Classes) !=50) AND (sum(Fees) !=51  )

Here is the weird part if you replace the != with an = like shown below then it works fine

with cte as 
( select 'A' as name , 10 as Classes , 11 as Fees union all
 select 'A' as name , 10 as Classes , 10 as Fees union all
  select 'A' as name , 10 as Classes , 10 as Fees union all
   select 'A' as name , 10 as Classes , 10 as Fees union all
    select 'A' as name , 10 as Classes , 10 as Fees union all
     select 'B' as name , 0 as Classes , 10 as Fees union all
      select 'B' as name , 0 as Classes , 10 as Fees union all
       select 'B' as name , 1 as Classes , 10 as Fees union all
        select 'B' as name , -10 as Classes , 10 as Fees union all
         select 'B' as name , 10 as Classes , 11 as Fees 
       )
       -- A has 50 rooms and 51 $ -- so it should not be returned
       -- B has 1 room and 51 $ and should be fetched
        -- why would the AND in the having clause  behave like a row level filter on only the indivigual column 
        -- instead of the combination
        -- The weird thing is the logic behaves as expected when using = but fails when using <>
       select name , sum(Classes) , sUM(Fees)
       from cte 
       group by name 
       having ( SUM(Classes) =1) AND (sum(Fees) =51  )

Am I missing something when it comes to the way Group by works? I know I can get around the issue but I don't understand why it behaves this way.

This is what I get

enter image description here

This is what I am supposed to get

enter image description here

Upvotes: 1

Views: 1069

Answers (2)

Thomas G
Thomas G

Reputation: 10216

You seem to not understand boolean logic.

without the having clause, those are your results

A   50  51
B   1   51

With

 having ( SUM(Classes) !=50) AND (sum(Fees) !=51  )

You ask: show me all rows for which classes are not 50 AND fees are not 51.

The AND is important.

Now look again at the results without having clause and ask yourself why would you expect anything to be returned?

Since both lines have a fee=51, nothing is returned because you ask for rows having fee different than 51.

This is a known mind trap from Boolean logic when you cumulate NOT (or <> !=) with ANDs. Generally speaking you should rarely do that. When you have several NOT, you should cumulate them with OR, not AND.

Upvotes: 0

T. Peter
T. Peter

Reputation: 887

after some discuss, try use this

 having not(SUM(Classes) =50 AND sum(Fees) =51)

and OP you gotta think twice when you writing boolean.

Upvotes: 1

Related Questions