COUNT

Window function

Description

The COUNT window function counts the rows defined by the expression.

(Redshift SQL Language Reference COUNT function)

This function is fully supported in Snowflake.

For more information about quoted identifiers in functions, click here.

Grammar Syntax

COUNT ( * | [ ALL ] expression) OVER
(
[ PARTITION BY expr_list ]
[ ORDER BY order_list frame_clause ]
)

Sample Source Patterns

Setup data

Redshift

CREATE TABLE sales_data (
    sale_id INT,
    product_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales_data (sale_id, product_id, sale_date, amount) VALUES
(1, 101, '2024-01-01', 200.00),
(2, 102, '2024-01-02', 150.00),
(3, 101, '2024-01-03', 300.00),
(4, 101, '2024-01-03', 250.00),
(5, 103, '2024-01-04', 450.00),
(6, 102, '2024-01-05', 100.00),
(7, 104, '2024-01-05', 500.00),
(8, 101, '2024-01-06', 350.00);

Input Code:

Redshift

SELECT
    product_id,
    COUNT(ALL amount) OVER (PARTITION BY product_id) AS count_all_amount,
    "count"(*) OVER() AS total_sales,
    "COUNT"(*) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_count
FROM
    sales_data
ORDER BY product_id;

Result

PRODUCT_ID	COUNT_ALL_AMOUNT	TOTAL_SALES	CUMULATIVE_COUNT
101	4	8	1
101	4	8	2
101	4	8	3
101	4	8	4
102	2	8	1
102	2	8	2
103	1	8	1
104	1	8	1

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
    product_id,
    COUNT(ALL amount) OVER (PARTITION BY product_id) AS count_all_amount,
    COUNT(*) OVER() AS total_sales,
    COUNT(*) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_count
FROM
    sales_data
ORDER BY product_id;

Result

PRODUCT_ID	COUNT_ALL_AMOUNT	TOTAL_SALES	CUMULATIVE_COUNT
101	4	8	1
101	4	8	2
101	4	8	3
101	4	8	4
102	2	8	1
102	2	8	2
103	1	8	1
104	1	8	1

Known Issues

No issues were found.

Related EWIs

There are no known issues.