ORA-00937: non-single component group function Error
select count(*), t.user_name from sys_user t, sys_department a, sys_dep_type d where t.dep_id = a.dep_id and a.dep_id = d.dep_id and t.recd_is_del = 0<strong>group by t.user_name</strong>
The key to this error is that the aggregate function, that is, count (*), is unable to operate the database. Since the aggregate function is specified and other columns are specified, you also want to group data based on specified columns ,. Just like you need to count the number of boys and girls in the class, but you cannot group them. You can only show them in one piece of data. Solution: Use group by to group them.
Aggregate functions,A function that calculates a GROUP of values and returns a single value. It is often used together with the group by clause of the SELECT statement.
1. AVG returns the average value in the specified group. The null value is ignored.
Example: select prd_no, avg (qty) from sales group by prd_no
2. COUNT returns the number of items in the specified group.
Example: select count (prd_no) from sales
3. MAX returns the maximum value of the specified data.
Example: select prd_no, max (qty) from sales group by prd_no
4. MIN returns the minimum value of the specified data.
Example: select prd_no, min (qty) from sales group by prd_no
5. SUM returns the SUM of the specified data and can only be used for numeric columns. null values are ignored.
Example: select prd_no, sum (qty) from sales group by prd_no
6. COUNT_BIG returns the number of projects in the specified group. Unlike the COUNT function, COUNT_BIG returns the bigint value, while COUNT returns the int value.
Example: select count_big (prd_no) from sales
7. GROUPING generates an additional column. When a row is added using the CUBE or ROLLUP operator, the output value is 1. when the added row is not produced by CUBE or ROLLUP, the output value is 0.
Example: select prd_no, sum (qty), grouping (prd_no) from sales group by prd_no with rollup
8. BINARY_CHECKSUM returns the binary check value calculated for the list of rows or expressions in the table, which is used to detect changes to the row in the table.
Example: select prd_no, binary_checksum (qty) from sales group by prd_no
9. CHECKSUM_AGG the check value of the specified data is returned. The null value is ignored.
Example: select prd_no, checksum_agg (binary_checksum (*) from sales group by prd_no
10. CHECKSUM returns the CHECKSUM calculated on the table row or expression list to generate a hash index.
11. STDEV returns the statistical standard deviation of all values in the given expression.
Example: select stdev (prd_no) from sales
12. STDEVP returns the population statistics standard deviation for all values in a given expression.
Example: select stdevp (prd_no) from sales
13. VAR returns the statistical variance of all values in the given expression.
Example: select var (prd_no) from sales
14. VARP returns the statistical variance for filling all values in a given expression.
Example: select varp (prd_no) from sales)
