[SQL] Mastering COUNT(*) OVER() in Oracle SQL

 

🔍 What is COUNT(*) OVER()?

COUNT(*) OVER() is an analytic (window) function in SQL that lets you compute a count without collapsing rows.

In traditional aggregation (like GROUP BY), you lose row-level detail because the result is grouped. But analytic functions like COUNT(*) OVER() retain all rows and just add an extra column with aggregated data relative to a window (a set of rows defined over the result set).

⚙️ Basic Syntax:

COUNT(*) OVER ([PARTITION BY ...] [ORDER BY ...])

COUNT(*) - Counts all rows
OVER() - Applies the count as a window function (not an aggregate)
PARTITION BY (optional) - Breaks data into groups like GROUP BY, but still retains all rows
ORDER BY (optional) - Defines the order in which the window function is applied

SELECT

    psv.segment1 Supplier_Number,

    psv.vendor_name Supplier_Name,

    psm.vendor_site_code Supplier_site,

    psv.vendor_id supplier_id,

    psm.vendor_site_id supplier_site_id,

    hou.organization_id bu_id,

    hou.name bu_name,

    COUNT(*) OVER() AS total_count

FROM

    poz_suppliers_v          psv,

    poz_supplier_sites_all_m psm,

    hr_operating_units       hou

WHERE

        1 = 1

    AND psv.vendor_id  = psm.vendor_id

    AND psm.prc_bu_id  = hou.organization_id

📋 Sample Output







🔍 Explanation:


There are 3 supplier records retrieved by the query. 
COUNT(*) OVER() returns "3" in every row, indicating the total number of rows that match the query. 
This avoids needing a second query just to get the total count, which is helpful for pagination or report summary headers.



📌 Differences: COUNT(*) vs COUNT(*) OVER()









🧠 Performance Tip:


Although powerful, COUNT(*) OVER() adds overhead because it processes entire result sets. Use it when necessary — especially in reporting, pagination, or when avoiding extra queries.




✅ Use Cases of COUNT(*) OVER()


Get total row count across all data:
SELECT emp_name, dept_id, COUNT(*) OVER() AS total_employees
FROM employees;

➡ Adds total_employees column to every row.

Get count per group (PARTITION BY):
SELECT emp_name, dept_id, COUNT(*) OVER(PARTITION BY dept_id) AS dept_count
FROM employees;

➡ Shows how many employees each department has, on every row.