You must Sign In to post a response.
  • Category: SQL Server

    Having clause with OR and AND operators

    Kindly copy the script below:

    CREATE TABLE #Test(Name VARCHAR(50), Salary INTEGER, DayCount INTEGER)
    INSERT INTO #Test VALUES ('AAA', 2000,10)
    INSERT INTO #Test VALUES('AAA', 4000,20)
    INSERT INTO #Test VALUES('BBB', 3000,0)
    INSERT INTO #Test VALUES('BBB', 1000,0)
    INSERT INTO #Test VALUES('CC', 0,30)
    INSERT INTO #Test VALUES('CC', 0,10)

    -- Query No 1
    SELECT Name, SUM(Salary) AS Salary, SUM(DayCount) AS DaysCount
    FROM #Test
    GROUP BY Name

    -- Query No 2
    SELECT Name, SUM(Salary) AS Salary, SUM(DayCount) AS DaysCount
    FROM #Test
    GROUP BY Name
    HAVING SUM(Salary) <> 0 OR SUM(DayCount) <> 0

    -- Query No 3
    SELECT Name, SUM(Salary) AS Salary, SUM(DayCount) AS DaysCount
    FROM #Test
    GROUP BY Name
    HAVING SUM(Salary) <> 0 AND SUM(DayCount) <> 0


    Please help me to understand the output of Query 2 and 3.
    According to me
    Query 2 should eliminate the rows related to name 'AAA' & 'BBB'
    Query 3 should not eliminate any rows.

    Thanks in advance.
  • #762337
    Hi

    you can try this Query for Query 2 should eliminate the rows related to name 'AAA' & 'BBB'


    Select * from #Test
    SELECT Name, SUM(Salary) AS Salary, SUM(DayCount) AS DaysCount FROM #Test where salary=0
    GROUP BY Name,Salary HAVING SUM(Salary) <> 0 or SUM(DayCount) <> 0 and Salary=0

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #762338
    Hi

    You can try this Query Query 3 should not eliminate any rows.


    SELECT Name, SUM(Salary) AS Salary, SUM(DayCount) AS DaysCount FROM #Test GROUP BY Name HAVING SUM(Salary) <> 0 or SUM(DayCount) <> 0

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #762344
    Hello Dotnet Developer,

    Thanks for your response.
    But, i do not need any alternative way to achieve.
    I want explanation, how SQL works with Having Clause with OR and AND operator, corresponding to my script.

  • #762432
    Hello Sandip Singh,

    Mr. Kumar's query :

    SELECT Name, SUM(Salary) AS Salary, SUM(DayCount) AS DaysCount FROM #Test where salary=0
    GROUP BY Name,Salary HAVING SUM(Salary) <> 0 or SUM(DayCount) <> 0 and Salary=0

    --> Query will return the numbers of rows which is having SUM of salary or daycount which is not is equal to zero and salary column which is not is equal to zero.

    You can go through the below link for Explanation of HAVING Clause with OR, AND Operators :

    https://msdn.microsoft.com/en-US/library/8hhs5f4e(v=vs.80).aspx


    Hope this will help you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"


Sign In to post your comments