So I tried running this simple code

select COUNT(Ename),BOSS
from EMPLOYEE
WHERE BOSS=7839

but seems I did not get the output unless I use a "group by" boss below. I am not able to understand why can't I get output without "group by".

Can anybody explain in laymen terms. Would be much appreciated.

CodePudding user response：

SELECT COUNT(*) FROM employee WHERE boss=7839 should work.

If you ask COUNT(*), name, the request would not be fulfillable, because which employee's name would be returned? The first employee's? The last? A random one? -- and so an error is raised: you cannot select the field unless you group by it. Some RDBMS might allow this, and use any of the previous options (MySQL uses the first row, I think).

If you ask COUNT(*), boss, the request is fulfillable even if useless, because the WHERE condition specifies that boss=7389. So there is no ambiguity, this is actually the same as asking SELECT COUNT(*), 7389 AS boss. But the RDBMS might not have the insight to understand this, since it is an edge case (it is legal SQL, and it could happen, but it's not so common except in contrived cases); and might raise the same error as before on general principles.

CodePudding user response：

You need GROUP BY because COUNT() is an aggregate function. Really, the best way to do this is by using the HAVING command:

SELECT COUNT(ename), boss
FROM employee
GROUP BY boss
HAVING boss = 7839

CodePudding user response：

Either you specify COUNT() alone, or you ask for COUNT(),boss but latter implies GROUP BY.

COUNT() is an "aggregate function", which means that, unlike regular functions (that can be evaluated for each row), these ones wait for the dataset to complete before they can give you an answer. That's case for SUM(), MIN(), MAX() or such as long it regards a whole column.

This means that you cannot ask for their value while the dataset is being parsed and, for this reason, cannot display their result aside the current value of a field (here being "boss"), because it just makes no sense. They're meant to be displayed as a bottom line after the data themselves.

However, you may do some groupings, according to a given criteria, that will sort your table along, then forming some subgroups each having a same value for the fields you specified in GROUP BY. These groups will each act as a mini-table upon which you aggregate functions can be evaluated.

CodePudding user response：

Count is an aggregate function. When you use Count (or any other aggregate function such as Count, Sum, Max, Min) next to select, then every other column item must be in group by. If you only use

select COUNT(Ename) -- there is no BOSS
from EMPLOYEE

then you don't have to use Group By. Lets say you have 5 columns: 1-COUNT(Ename), 2- BOSS, 3- column3, 4- column4, 5-column5. Then you would have to write select code as below:

select COUNT(Ename),BOSS, column3, column4, column5
from EMPLOYEE
WHERE BOSS=7839
GROUP BY
   BOSS, column3, column4, column5