why can't I use grouping functions without group by?-CodePudding

In the following code, why can't I use count () for each row in the table? The count() must return the number of colours of each row, which all become one, but it does not. If I use group by, it will.

select colour,count(*) 
from bricks;

updated: another example:

select b.*,
(select count(*) from bricks where colour=b.colour)
from bricks b;

The above example used grouping function without group by and it works without any errors

CodePudding user response：

That's just the way SQL demands it. You can't select a discrete column and and aggregate function without listing the discrete columns in a group by statement.

Your second example runs a subquery for each record returned from the main bricks query. This won't perform as well as the first query using a group by.

And you best alias your subquery table - I've seen circumstances where the parser decides you're essentially saying b.colour = b.colour, which will give you the same count(*) for each record in bricks.

CodePudding user response：

I don't have those bricks, but - I do have Scott's sample EMP table with jobs instead.

This is what you tried (and it doesn't work):

SQL> select job, count(*)
  2  from emp
  3  order by 1, 2;
select job, count(*)
       *
ERROR at line 1:
ORA-00937: not a single-group group function

This is what works, but - that's not what you want:

SQL> select job, count(*)
  2  from emp
  3  group by job
  4  order by 1, 2;

JOB         COUNT(*)
--------- ----------
ANALYST            2
CLERK              4
MANAGER            3
PRESIDENT          1
SALESMAN           4

This is what you want (and it works), but - doesn't look pretty:

SQL> select e.job,
  2    (select count(*) from emp a where a.job = e.job) cnt
  3  from emp e
  4  order by 1, 2;

JOB              CNT
--------- ----------
ANALYST            2
ANALYST            2
CLERK              4
CLERK              4
CLERK              4
CLERK              4
MANAGER            3
MANAGER            3
MANAGER            3
PRESIDENT          1
SALESMAN           4
SALESMAN           4
SALESMAN           4
SALESMAN           4

14 rows selected.

Good news for you - use count function in its analytic form:

SQL> select job, count(*) over (partition by job order by null) cnt
  2  from emp
  3  order by 1, 2;

JOB              CNT
--------- ----------
ANALYST            2
ANALYST            2
CLERK              4
CLERK              4
CLERK              4
CLERK              4
MANAGER            3
MANAGER            3
MANAGER            3
PRESIDENT          1
SALESMAN           4
SALESMAN           4
SALESMAN           4
SALESMAN           4

14 rows selected.

SQL>