GROUP BY Clause for Oracle PL/SQL
GROUP BY Clause Oracle PL/SQL
Normal GROUP BY
A few things to remember about the GROUP BY clause for Oracle.
- Any fields not part of an aggregate function (i.e. MAX() , SUM() ) in the SELECT clause must be listed in the GROUP BY clause.
- Limiting data returned for any aggregate function must be listed in the HAVING clause and not the WHERE clause.
1 2 3 4 5 | SELECT A.BUSINESS_UNIT, A.ENTERED_DT, SUM(A.GROSS_AMT), COUNT(*) AS CNT FROM PS_VOUCHER A WHERE BUSINESS_UNIT IN('00000', '00001', '00002', '00003') HAVING COUNT(*) > 5 OR SUM(A.GROSS_AMT) > 100000 GROUP BY A.BUSINESS_UNIT, A.ENTERED_DT |
With Hints
You must use the hint in the GROUP BY function exactly the same as the non-aggregate function
1 2 3 | SELECT /*+ INDEX(A PS1VOUCHER) */ A.DUP_INVOICE_STATUS, A.ENTERED_DT FROM PS_VOUCHER A GROUP BY /*+ INDEX(A PS1VOUCHER) */ A.DUP_INVOICE_STATUS, A.ENTERED_DT |
With CASE Statement
Example of GROUP BY with the CASE statement
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT COUNT(*), CASE WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <= 0 THEN 'PO NOT RECD PRIOR TO INV' WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <=.033 THEN 'PO RECD DAY BEFORE INV' ELSE 'PO RECD PRIOR TO PO' END AS RECVD FROM PS_VOUCHER WHERE ORIGIN IN('ONL', 'BAT') AND ENTERED_DT > SYSDATE - 30 GROUP BY CASE WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <= 0 THEN 'PO NOT RECD PRIOR TO INV' WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <=.033 THEN 'PO RECD DAY BEFORE INV' ELSE 'PO RECD PRIOR TO PO' END |