ORA-00979 - Not a GROUP BY Expression

ORA-00979: Not a GROUP BY Expression

The ORA-00979 error occurs when a SELECT statement contains columns in the SELECT list that are not included in the GROUP BY clause, while using aggregate functions. Oracle requires all non-aggregate columns in the SELECT list to be specified in the GROUP BY clause.

Use at Your Own Risk

This documentation is for educational purposes only. Always test solutions in development environments before production use. See our disclaimer for full terms.

Error Details

• Error Code: ORA-00979
• Error Message: “not a GROUP BY expression”
• Error Type: SQL Syntax Error
• Severity: Compilation Error

Common Causes

1. Missing GROUP BY Clause

Using aggregate functions without proper GROUP BY clause.

2. Incomplete GROUP BY List

Not including all non-aggregate columns in the GROUP BY clause.

3. Column Alias Issues

Using column aliases incorrectly in GROUP BY clause.

4. Expression Mismatches

Differences between SELECT list expressions and GROUP BY expressions.

5. Subquery Problems

Incorrect use of aggregate functions in subqueries.

Examples and Solutions

Example 1: Missing GROUP BY Clause

Problem:

-- Trying to mix aggregate and non-aggregate columns
SELECT department_id, COUNT(*)
FROM employees;
-- ORA-00979: not a GROUP BY expression

Solution:

-- Add GROUP BY clause for non-aggregate columns
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

-- Or remove non-aggregate column if not needed
SELECT COUNT(*)
FROM employees;

Example 2: Incomplete GROUP BY List

Problem:

-- Multiple non-aggregate columns, incomplete GROUP BY
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id;  -- Missing job_id
-- ORA-00979: not a GROUP BY expression

Solution:

-- Include all non-aggregate columns in GROUP BY
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id;

-- Alternative: Use only one grouping column
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

Example 3: Column Alias in GROUP BY

Problem:

-- Using alias in GROUP BY (Oracle doesn't allow this in all cases)
SELECT department_id AS dept, COUNT(*) AS emp_count
FROM employees
GROUP BY dept;  -- May cause ORA-00979 in some Oracle versions
-- ORA-00979: not a GROUP BY expression

Solution:

-- Use actual column name in GROUP BY
SELECT department_id AS dept, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;

-- Or use column position number
SELECT department_id AS dept, COUNT(*) AS emp_count
FROM employees
GROUP BY 1;

Example 4: Expression Mismatch

Problem:

-- Expression in SELECT doesn't match GROUP BY
SELECT UPPER(last_name), COUNT(*)
FROM employees
GROUP BY last_name;  -- Missing UPPER function
-- ORA-00979: not a GROUP BY expression

Solution:

-- Match expressions exactly
SELECT UPPER(last_name), COUNT(*)
FROM employees
GROUP BY UPPER(last_name);

-- Or simplify to use base column
SELECT last_name, COUNT(*)
FROM employees
GROUP BY last_name;

Example 5: Complex Expressions

Problem:

-- Complex expression mismatch
SELECT
    CASE
        WHEN salary > 5000 THEN 'High'
        ELSE 'Low'
    END AS salary_category,
    COUNT(*)
FROM employees
GROUP BY salary;  -- Wrong grouping expression
-- ORA-00979: not a GROUP BY expression

Solution:

-- Group by the exact expression
SELECT
    CASE
        WHEN salary > 5000 THEN 'High'
        ELSE 'Low'
    END AS salary_category,
    COUNT(*)
FROM employees
GROUP BY
    CASE
        WHEN salary > 5000 THEN 'High'
        ELSE 'Low'
    END;

-- Alternative: Use subquery
SELECT salary_category, COUNT(*)
FROM (
    SELECT
        CASE
            WHEN salary > 5000 THEN 'High'
            ELSE 'Low'
        END AS salary_category
    FROM employees
)
GROUP BY salary_category;

Example 6: Date Functions

Problem:

-- Date function in SELECT but not in GROUP BY
SELECT TO_CHAR(hire_date, 'YYYY'), COUNT(*)
FROM employees
GROUP BY hire_date;  -- Should group by the expression
-- ORA-00979: not a GROUP BY expression

Solution:

-- Group by the exact date expression
SELECT TO_CHAR(hire_date, 'YYYY') AS hire_year, COUNT(*)
FROM employees
GROUP BY TO_CHAR(hire_date, 'YYYY');

-- Alternative: Use EXTRACT function
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year, COUNT(*)
FROM employees
GROUP BY EXTRACT(YEAR FROM hire_date);

Advanced Examples

Multiple Table Joins

Problem:

-- Join with aggregation
SELECT d.department_name, e.job_id, AVG(e.salary)
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;  -- Missing e.job_id
-- ORA-00979: not a GROUP BY expression

Solution:

-- Include all non-aggregate columns
SELECT d.department_name, e.job_id, AVG(e.salary)
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name, e.job_id;

Window Functions (Alternative Approach)

Problem:

-- Wanting to show detail with aggregates
SELECT employee_id, last_name, department_id, COUNT(*)
FROM employees
GROUP BY department_id;  -- Can't include employee details
-- ORA-00979: not a GROUP BY expression

Solution:

-- Use window functions instead
SELECT
    employee_id,
    last_name,
    department_id,
    COUNT(*) OVER (PARTITION BY department_id) AS dept_count
FROM employees;

-- Or use correlated subquery
SELECT
    employee_id,
    last_name,
    department_id,
    (SELECT COUNT(*)
     FROM employees e2
     WHERE e2.department_id = e1.department_id) AS dept_count
FROM employees e1;

Subquery Aggregation

Problem:

-- Incorrect subquery with aggregation
SELECT department_id,
       (SELECT last_name, COUNT(*)
        FROM employees e2
        WHERE e2.department_id = e1.department_id)
FROM employees e1;
-- ORA-00979: not a GROUP BY expression (in subquery)

Solution:

-- Fix subquery aggregation
SELECT department_id,
       (SELECT COUNT(*)
        FROM employees e2
        WHERE e2.department_id = e1.department_id) AS emp_count
FROM employees e1
GROUP BY department_id;

-- Or use proper join
SELECT d.department_id, COUNT(e.employee_id) AS emp_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id;

Diagnostic Queries

Validate GROUP BY Requirements

-- Check which columns need to be in GROUP BY
-- This query helps identify the issue
SELECT DISTINCT table_name, column_name
FROM user_tab_columns
WHERE table_name IN ('EMPLOYEES', 'DEPARTMENTS')
ORDER BY table_name, column_name;

Test Query Structure

-- Start simple and build complexity
-- Step 1: Basic aggregation
SELECT COUNT(*) FROM employees;

-- Step 2: Add one grouping column
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

-- Step 3: Add more columns incrementally
SELECT department_id, job_id, COUNT(*)
FROM employees
GROUP BY department_id, job_id;

Analyze Existing Queries

-- Find queries with GROUP BY in SQL cache
SELECT sql_text
FROM v$sql
WHERE UPPER(sql_text) LIKE '%GROUP BY%'
AND UPPER(sql_text) LIKE '%SELECT%'
ORDER BY last_active_time DESC;

Resolution Strategies

1. Add Missing Columns to GROUP BY

-- Original problematic query
SELECT col1, col2, SUM(col3)
FROM table1
GROUP BY col1;  -- Add col2

-- Fixed query
SELECT col1, col2, SUM(col3)
FROM table1
GROUP BY col1, col2;

2. Remove Columns from SELECT

-- Remove non-essential columns
SELECT SUM(salary), AVG(salary)
FROM employees
-- No GROUP BY needed if no non-aggregate columns

3. Use Subqueries

-- Move aggregation to subquery
SELECT emp_data.*, dept_stats.avg_salary
FROM employees emp_data
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_stats ON emp_data.department_id = dept_stats.department_id;

4. Use Window Functions

-- Replace GROUP BY with window functions
SELECT
    employee_id,
    last_name,
    department_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;

Best Practices

1. Query Design Rules

-- Rule: Every column in SELECT (except aggregates) must be in GROUP BY
-- Good example
SELECT department_id, job_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id, job_id;

-- Bad example (missing job_id in GROUP BY)
-- SELECT department_id, job_id, COUNT(*), AVG(salary)
-- FROM employees
-- GROUP BY department_id;

2. Use Column Positions

-- Use positional notation for complex expressions
SELECT
    CASE WHEN salary > 5000 THEN 'High' ELSE 'Low' END,
    COUNT(*)
FROM employees
GROUP BY 1;  -- Position 1 refers to the CASE expression

3. Consistent Expression Usage

-- Keep expressions consistent between SELECT and GROUP BY
SELECT TRUNC(hire_date, 'MM') AS hire_month, COUNT(*)
FROM employees
GROUP BY TRUNC(hire_date, 'MM');  -- Exact same expression

4. Documentation and Comments

-- Document complex grouping logic
SELECT
    -- Grouping by department and job
    department_id,
    job_id,
    -- Aggregated metrics
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY
    department_id,  -- All employees in same department
    job_id;         -- Further grouped by job role

Common Patterns and Solutions

Pattern 1: Reporting Queries

-- Monthly sales report
SELECT
    TO_CHAR(order_date, 'YYYY-MM') AS order_month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY order_month;

Pattern 2: Hierarchical Grouping

-- Department and job level summary
SELECT
    d.department_name,
    e.job_id,
    COUNT(*) AS employee_count,
    AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name, e.job_id
ORDER BY d.department_name, e.job_id;

Pattern 3: Conditional Aggregation

-- Summary with conditional counts
SELECT
    department_id,
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN salary > 5000 THEN 1 END) AS high_earners,
    COUNT(CASE WHEN salary <= 5000 THEN 1 END) AS regular_earners
FROM employees
GROUP BY department_id;

Related Errors

• ORA-00937: Not a Single-Group Group Function
• ORA-00934: Group Function Not Allowed Here
• ORA-00936: Missing Expression
• ORA-00904: Invalid Identifier

Summary

ORA-00979 occurs when non-aggregate columns in the SELECT list are not included in the GROUP BY clause. Resolution requires either adding missing columns to GROUP BY, removing columns from SELECT, or restructuring the query using subqueries or window functions. Always ensure that every non-aggregate column in the SELECT list appears in the GROUP BY clause with the exact same expression.