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

Syed Arbab Ahmed

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) <=10

Please do guide in Oracle and in SQL.

Thanks in advance

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Syed Arbab Ahmed15y ago
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.

???
Raja Krishnamurthy15y ago
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) <=10

I apologize if my assumption is wrong.

HTH.

Happy Programming!!!

Regards,
Raja

andrey robianes15y ago

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

result:

Syed Arbab Ahmed15y ago
This query:

SELECT SUM(ACCOUNT_TYPE)
FROM TBLCUSTCHANNEL
HAVING SUM(ACCOUNT_TYPE) <=10

giving this error:

Table or view does not exist.

& This query:

SELECT SUM(ACCOUNT_TYPE)
FROM TBLCUSTCHANNEL
where SUM(ACCOUNT_TYPE) <=10

giving this error:

Group function is not allowed here.
Suthish Nair15y ago
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 robianes15y ago
HAVING syntax just using when you use GROUP BY clause,,

for the other syntax using just WHERE clause,,
Syed Arbab Ahmed16y ago
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.
Manikavelu Velayutham16y ago
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) <=10