How to Resolve ORA-00979: Grouping Columns in SQL Queries?

Understanding and Resolving ORA-00979 in SQL Queries

The ORA-00979 error, "not a GROUP BY expression," arises when a SQL query uses a GROUP BY clause incorrectly. This happens when the SELECT statement includes columns not mentioned in the GROUP BY clause, or uses aggregate functions on columns without grouping.

The GROUP BY clause groups rows with identical values in specified columns, allowing for aggregate functions (like SUM, AVG, MIN, MAX, COUNT) to summarize data within each group. Without proper grouping, the database cannot determine which values to associate with each group, leading to the error.

Example and Solution:

A common scenario causing this error involves selecting multiple columns, but only grouping by a subset. The database is unable to determine a single value for the ungrouped columns within each group.

To fix this, either:

1. Include all non-aggregated columns in the GROUP BY clause: This is the simplest solution if you want to see all unique combinations of the selected columns.

2. Use aggregate functions on non-grouped columns: If you only need summary statistics for certain columns, apply aggregate functions (like MIN, MAX, or AVG) to those columns. This will reduce the output to a single row per group.

Let's illustrate with a corrected query:

SELECT 
    cr.review_sk, 
    cr.cs_sk, 
    cr.full_name,
    MIN(TO_CHAR(cf.fact_date, 'mm/dd/yyyy')) AS appt, -- Using MIN to aggregate the date
    cs.cs_id, 
    cr.tracking_number
FROM 
    review cr, cs, fact cf
WHERE 
    cr.cs_sk = cs.cs_sk
    AND UPPER(cs.cs_id) LIKE '%' || UPPER(i_cs_id) || '%'
    AND row_delete_date_time IS NULL
    AND cr.review_sk = cf.review_wk (+)
    AND cr.fact_type_code (+) = 183050
GROUP BY 
    cr.review_sk, cr.cs_sk, cr.full_name, cs.cs_id, cr.tracking_number -- All non-aggregated columns are now included
ORDER BY 
    cs.cs_id, cr.full_name;

By correctly including all non-aggregated columns in the GROUP BY clause, the query will execute without the ORA-00979 error, ensuring data integrity and providing the expected results. Remember to choose the approach (including all columns or using aggregate functions) that best suits your data analysis needs.

The above is the detailed content of How to Resolve ORA-00979: Grouping Columns in SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!