Summary: in this tutorial, you’ll learn how to use the PostgreSQL COUNT() window function to count the number of rows within a partition.
Introduction to PostgreSQL COUNT() Window Function #
In PostgreSQL, the COUNT() window function returns the number of rows within a partition. Here’s the syntax of the COUNT() window function:
COUNT(value) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression]
)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)In this syntax:
valueis a column in which you want to count the values. If you use star (*) theCOUNT()function will count the rows.- The
OVERdefines the window frame where theCOUNT()function applies. PARTITION BYdivides the result set into partitions by thepartition_expression. Thepartition_expressioncan be one or more columns. If you omit thePARTITION BYclause, theCOUNT()returns total number of rows in the result set.ORDER BYclause determines the order of rows within each partition.
PostgreSQL COUNT Window Function examples #
Let’s take some examples of using the PostgreSQL COUNT() window function with the products table:
Counting products #
The following example uses the COUNT() window function to return the number of products along with the current product:
SELECT
product_id,
product_name,
COUNT(*) OVER () AS total_products
FROM
products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
product_id | product_name | total_products
------------+----------------------------+----------------
1 | Samsung Galaxy S24 | 25
2 | Apple iPhone 15 | 25
3 | Huawei Mate 60 | 25
4 | Xiaomi Mi 14 | 25
5 | Sony Xperia 1 VI | 25
6 | Samsung Galaxy Z Fold 5 | 25
7 | Apple iPhone 15 Pro Max | 25
8 | Samsung Galaxy Tab S9 | 25
9 | Apple iPad Pro 12.9 | 25
10 | Apple AirPods Pro 3 | 25
11 | Samsung Galaxy Buds Pro 2 | 25
12 | Apple Watch Series 9 | 25
13 | Samsung Galaxy Watch 6 | 25
14 | LG OLED TV C3 | 25
15 | Sony Bravia XR A95K | 25
16 | Samsung QN900C Neo QLED | 25
17 | LG G3 OLED | 25
18 | Sony HT-A7000 Soundbar | 25
19 | Bose SoundLink Max | 25
20 | Dell XPS 15 | 25
21 | HP Spectre x360 | 25
22 | Microsoft Surface Laptop 5 | 25
23 | Lenovo ThinkPad X1 Carbon | 25
24 | Apple iMac 24" | 25
25 | Dell Inspiron 27 | 25Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)In this query, COUNT(*) OVER () returns the number of rows in the products table. The result set includes each product along with the total number of products.
Counting products for each brand #
The following query uses the COUNT() window function with PARTITION BY clause to return the number of products for each brand:
SELECT
brand_id,
product_id,
product_name,
COUNT(*) OVER (PARTITION BY brand_id) AS products_per_brand
FROM
products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
brand_id | product_id | product_name | products_per_brand
----------+------------+----------------------------+--------------------
1 | 1 | Samsung Galaxy S24 | 6
1 | 6 | Samsung Galaxy Z Fold 5 | 6
1 | 8 | Samsung Galaxy Tab S9 | 6
1 | 11 | Samsung Galaxy Buds Pro 2 | 6
1 | 13 | Samsung Galaxy Watch 6 | 6
1 | 16 | Samsung QN900C Neo QLED | 6
2 | 9 | Apple iPad Pro 12.9 | 6
2 | 10 | Apple AirPods Pro 3 | 6
2 | 2 | Apple iPhone 15 | 6
2 | 12 | Apple Watch Series 9 | 6
2 | 24 | Apple iMac 24" | 6
2 | 7 | Apple iPhone 15 Pro Max | 6
3 | 3 | Huawei Mate 60 | 1
4 | 4 | Xiaomi Mi 14 | 1
5 | 5 | Sony Xperia 1 VI | 3
5 | 15 | Sony Bravia XR A95K | 3
5 | 18 | Sony HT-A7000 Soundbar | 3
6 | 17 | LG G3 OLED | 1
7 | 25 | Dell Inspiron 27 | 4
7 | 14 | LG OLED TV C3 | 4
7 | 19 | Bose SoundLink Max | 4
7 | 20 | Dell XPS 15 | 4
8 | 21 | HP Spectre x360 | 1
9 | 22 | Microsoft Surface Laptop 5 | 1
10 | 23 | Lenovo ThinkPad X1 Carbon | 1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)In this query:
PARTITION BYdivides the rows into partitions bybrand_id.COUNT(*) OVER (PARTITION BY brand_id)returns the number of products within each brand.
Calculating the percentage of products within each brand #
The following statement uses the COUNT() window function to calculate the percentage of products within each brand:
SELECT
product_name,
brand_id,
COUNT(*) OVER (PARTITION BY brand_id) AS products_per_brand,
COUNT(*) OVER () AS total_products,
(COUNT(*) OVER (PARTITION BY brand_id) * 100.0 / COUNT(*) OVER ())::DEC(5, 2) AS percentage_per_brand
FROM
products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
product_name | brand_id | products_per_brand | total_products | percentage_per_brand
----------------------------+----------+--------------------+----------------+----------------------
Samsung Galaxy S24 | 1 | 6 | 25 | 24.00
Samsung Galaxy Z Fold 5 | 1 | 6 | 25 | 24.00
Samsung Galaxy Tab S9 | 1 | 6 | 25 | 24.00
Samsung Galaxy Buds Pro 2 | 1 | 6 | 25 | 24.00
Samsung Galaxy Watch 6 | 1 | 6 | 25 | 24.00
Samsung QN900C Neo QLED | 1 | 6 | 25 | 24.00
Apple iPad Pro 12.9 | 2 | 6 | 25 | 24.00
Apple AirPods Pro 3 | 2 | 6 | 25 | 24.00
Apple iPhone 15 | 2 | 6 | 25 | 24.00
Apple Watch Series 9 | 2 | 6 | 25 | 24.00
Apple iMac 24" | 2 | 6 | 25 | 24.00
Apple iPhone 15 Pro Max | 2 | 6 | 25 | 24.00
Huawei Mate 60 | 3 | 1 | 25 | 4.00
Xiaomi Mi 14 | 4 | 1 | 25 | 4.00
Sony Xperia 1 VI | 5 | 3 | 25 | 12.00
Sony Bravia XR A95K | 5 | 3 | 25 | 12.00
Sony HT-A7000 Soundbar | 5 | 3 | 25 | 12.00
LG G3 OLED | 6 | 1 | 25 | 4.00
Dell Inspiron 27 | 7 | 4 | 25 | 16.00
LG OLED TV C3 | 7 | 4 | 25 | 16.00
Bose SoundLink Max | 7 | 4 | 25 | 16.00
Dell XPS 15 | 7 | 4 | 25 | 16.00
HP Spectre x360 | 8 | 1 | 25 | 4.00
Microsoft Surface Laptop 5 | 9 | 1 | 25 | 4.00
Lenovo ThinkPad X1 Carbon | 10 | 1 | 25 | 4.00Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)In this query:
COUNT(*) OVER (PARTITION BY brand_id)returns the number of products per brand.COUNT(*) OVER ()returns the total number of products.(COUNT(*) OVER (PARTITION BY brand_id) * 100.0 / COUNT(*) OVER ())returns the percentage of products within each brand.
Summary #
- Use the
COUNT()window function to return the number of rows within a partition.
Quiz #
Was this tutorial helpful ?
