Can we use aggregate function in GROUP BY clause?
The GROUP BY clause is normally used along with five built-in, or “aggregate” functions. These functions perform special operations on an entire table or on a set, or group, of rows rather than on each row and then return one row of values for each group.
Can we use GROUP BY clause without aggregate function?
You can use the GROUP BY clause without applying an aggregate function. In this case, the GROUP BY works like the DISTINCT clause that removes duplicate rows from the result set.
Can we use aggregate function in where clause in Oracle?
Aggregate functions cannot be used in a WHERE clause. Its violation will produce the Oracle ORA-00934 group function is not allowed here error message.
When to use an aggregation function instead of a key?
The unaggregated columns in the SELECT of an aggregation query need to match the keys. In your query, BILLDATE is not aggregated and it is not a key. If you want only one row per BILLNO — or if you know that BILLDATE is the same for all BILLNO — then you can use an aggregation function instead:
Where does column appear in group by clause?
According to the official documentation，‘The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list. However, each table or view column in any nonaggregate expression in the list must be included in the GROUP BY list’， it will indicate the cause of your error.
Why is billdate not in group by clause?
Column ‘PURCHASE.BILLDATE’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. The error is pretty obvious. The unaggregated columns in the SELECT of an aggregation query need to match the keys. In your query, BILLDATE is not aggregated and it is not a key.
What to do when billdate is not aggregated in SQL?
In your query, BILLDATE is not aggregated and it is not a key. The simple fix is: SELECT BILLNO, BILLDATE, SUM(QTY) AS SUMQTY, SUM(AMOUNT) AS SUMAMOUNT FROM PURCHASE GROUP BY BILLNO, BILLDATE; If you want only one row per BILLNO– or if you know that BILLDATE is the same for all BILLNO– then you can use an aggregation function instead: