SQL COUNT- not a single group group function error

Question

I run the following COUNT(*) query on Oracle DB:

select count(*) + (select count(*) from t_diagram)  from t_object

I get the following error:

Not a single group- group function.

I understand that using aggregation methods (e.g. SUM, AVG) require a GROUP BY statement.
However, how can I add a GROUP BY in a select COUNT (*) query?

Another challenge: The application I run the query on, does not suppport DUALs. It supports SELECT statements only. Any ideas?

You need to give us more information. COUNT(*) is an aggregate, and will be applied to each group in the query. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 4, 2015 at 5:49
Can you please post the exact query, using only count(*) should not give this error. — vishad
– vishad, Commented Jun 4, 2015 at 5:52
alone select COUNT(*) from table won't give you this error. You must be doing something else as well. Show your full query. — Amnesh Goel
– Amnesh Goel, Commented Jun 4, 2015 at 6:00
What is this query? What is your inner query doing? or do you need count() of both tables i.e. t_object and t_diagram? or you need count() based on some condition? — Amnesh Goel
– Amnesh Goel, Commented Jun 4, 2015 at 6:04

Giorgi Nakeuri · Accepted Answer · 2015-06-04 06:04:16Z

4

You can rewrite as:

select (select count(*) from t_object) + (select count(*) from t_diagram) from dual

Fiddle http://sqlfiddle.com/#!4/3d588/1

answered Jun 4, 2015 at 6:04

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

user3165438 Over a year ago

@GiorgyNakeuri, Thanks, In theory it should work (I unserstand dual is temporary table ) , but in reality- it makes the application fail.

user3165438 Over a year ago

The application simply crushed down. Any ideas?

Giorgi Nakeuri Over a year ago

It is not because of select...Select should work fine. Embed your code in try catch blocks and debug.

Giorgi Nakeuri Over a year ago

@user3165438, I don't know what app you are using but, dual is supported by Oracle engine. Oracle will give just a one row with one column. Dual has no busineess with your app, Oracle does. May be you shoud just alias the expression? select (select count(*) from t_object) + (select count(*) from t_diagram) AS SomeName from dual

user3165438 Over a year ago

Thanks. Works great, but instead of retreiving the sum of counts, it just chaining the results. Any other approach?

|

jarlh · Accepted Answer · 2015-06-04 06:33:50Z

3

Use a derived table?

select sum(cnt)
from
(
select count(*) as cnt from t_object
union all
select count(*) as cnt from t_diagram
) dt

answered Jun 4, 2015 at 6:33

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

SQL COUNT- not a single group group function error

2 Answers 2

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related