How to use 'HAVING' without 'GROUP BY', what's wrong in this query?

Question

Hi! ' Having ' can be used without 'Group by' as well, table has the data but this query is not returning any answers is there anything wrong with this query? SELECT SUM(ACCOUNT_TYPE) FROM TBLCUSTCHANNEL HAVING SUM(ACCOUNT_TYPE) Please do guide in Oracle and in SQL. Thanks in advance

Raja Krishnamurthy · Answer

First of all what are you trying to achieve from your query? Based on the description of the table names and column name I think you want to get the Account_Types with less than 10 customers. If that is the case then the following query should help: SELECT Account_Type, Count(Account_Type) FROM TBLCUSTCHANNEL GROUP BY Account_Type Having Count(Account_Type) I apologize if my assumption is wrong. HTH. Happy Programming!!! Regards, Raja

Manikavelu Velayutham · Answer

Your query is correct. Does the query throw any error ? What output it generates ? if the values is null, then there might be a row which the Account_type value is null. becuase null + anything = null While summing up, there should not be any row that contain null value. you put like condition Where Account_Type IS NOT NULL like below SELECT SUM(ACCOUNT_TYPE) FROM TBLCUSTCHANNEL WHERE ACCOUNT_TYPE IS NOT NULL HAVING SUM(ACCOUNT_TYPE)

Syed Arbab Ahmed · Answer

Logical error, as I wanted to have some of account_type less than or equal to 0 There was a logical error in my query as I wanted to use sum function for those account_type whose values are less than or equal to 10 , but the above query actually doing sum with all records of account_type, and answer was not showing because there sum was above 10. So either I have to use subquery or I don't know how much I can play with this query to have my desired result. ???

andrey robianes · Answer

SELECT SUM(salary) FROM employees HAVING SUM(salary) >=10 result:

Syed Arbab Ahmed · Answer

This query: SELECT SUM(ACCOUNT_TYPE) FROM TBLCUSTCHANNEL HAVING SUM(ACCOUNT_TYPE) giving this error: Table or view does not exist. & This query: SELECT SUM(ACCOUNT_TYPE) FROM TBLCUSTCHANNEL where SUM(ACCOUNT_TYPE) giving this error: Group function is not allowed here.

Suthish Nair · Answer

Sayed, remove the having statement and check what sum you getting. The sum value will be greater than having condition, that might be the reason no rows getting returned.

andrey robianes · Answer

HAVING syntax just using when you use GROUP BY clause,, for the other syntax using just WHERE clause,,

Syed Arbab Ahmed · Answer

1-No there is no syntax error may be there is logical error that I am not getting. 2-SUM(ACCOUNT_TYPE) column comes but without any data and i know that in table there is relevant data. 3-No Account_Type is 'NULL', in TBLCUSTCHANNEL there should be Account_Type.

How to use 'HAVING' without 'GROUP BY', what's wrong in this query?

Answers (8)