COUNT

Aggregate function

Description

The COUNT function counts the rows defined by the expression.

(Redshift SQL Language Reference COUNT function)

COUNT and APPROXIMATE COUNT are fully supported in Snowflake by COUNT and APPROX_COUNT_DISTINCT.

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

Grammar Syntax

COUNT ( * | expression )

COUNT ( [ DISTINCT | ALL ] expression )

APPROXIMATE COUNT ( DISTINCT expression )

Sample Source Patterns

Setup data

Redshift

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    sale_amount DECIMAL(10, 2),
    sale_date DATE,
    customer_id INT
);

INSERT INTO sales (sale_id, product_id, sale_amount, sale_date, customer_id) VALUES
(1, 101, 100.00, '2024-01-01', 1001),
(2, 102, 200.00, '2024-01-01', 1002),
(3, 101, 150.00, '2024-01-02', 1001),
(4, 103, 250.00, '2024-01-02', 1003),
(5, 102, 300.00, '2024-01-03', 1002),
(6, 101, 200.00, '2024-01-03', 1004),
(7, 101, 120.00, '2024-01-04', 1001),
(8, 102, 180.00, '2024-01-04', 1005),
(9, 103, 300.00, '2024-01-05', 1003),
(10, 101, 130.00, '2024-01-05', 1006),
(10, 101, 130.00, '2024-01-05', 1006);

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT
    product_id,
    COUNT(sale_id) AS total_sales,
    "COUNT"(DISTINCT sale_id) AS total_sales_distinct,
    "count"(ALL sale_id) AS total_sales_all,
    COUNT(*) AS total_sales_asterisk,
    APPROXIMATE COUNT ( DISTINCT sale_id) AS aprroximate_count_total_sales
FROM
    sales
GROUP BY
    product_id
ORDER BY
    total_sales DESC;

Result

PRODUCT_ID	TOTAL_SALES	TOTAL_SALES_DISTINCT	TOTAL_SALES_ALL	TOTAL_SALES_ASTERISK	APRROXIMATE_COUNT_TOTAL_SALES
101	6	5	6	6	5
102	3	3	3	3	3
103	2	2	2	2	2

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
    product_id,
    COUNT(sale_id) AS total_sales,
    COUNT(DISTINCT sale_id) AS total_sales_distinct,
    COUNT(ALL sale_id) AS total_sales_all,
    COUNT(*) AS total_sales_asterisk,
    APPROX_COUNT_DISTINCT ( DISTINCT sale_id) AS aprroximate_count_total_sales
FROM
    sales
GROUP BY
    product_id
ORDER BY
    total_sales DESC;

Result

PRODUCT_ID	TOTAL_SALES	TOTAL_SALES_DISTINCT	TOTAL_SALES_ALL	TOTAL_SALES_ASTERISK	APRROXIMATE_COUNT_TOTAL_SALES
101	6	5	6	6	5
102	3	3	3	3	3
103	2	2	2	2	2

Known Issues

No issues were found.

Related EWIs

There are no known issues.