COUNT

Calculates either the number of non-NULL values in the specified column or the total number of records in the table.

Syntax

Aggregate Function

COUNT([DISTINCT] expr)

COUNT(*)

Window Function

COUNT([DISTINCT] expr) OVER (
                                [PARTITION BY part]
                                [ORDER BY ord [ASC|DESC] [window_frame]]
                                )

Arguments

expr: A (qualified) column name.

---

part (optional): Expression to partition by.

---

ord (optional): Optional expression to sort within each partition.

---

window_frame: Defines the subset of rows within the window for which the aggregated values are to be returned.

Example 1 | Aggregate Function and 'DISTINCT' Clause

select  count(*),
        count(col1) as result1,
        count(DISTINCT col1) as result2,
        count(col2) as result3,
        count(DISTINCT col2) as result4
             from  (select 'a' as ord1, 'g1' as grp1, '10' as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, '40' as col1, 40 as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, '60' as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, '50' as col1, 50 as col2                    
                    union all
                    select NULL as ord1, NULL as grp1, NULL as col1, NULL as col2);

+----------+---------+---------+---------+---------+
| COUNT(*) | RESULT1 | RESULT2 | RESULT3 | RESULT4 |
+----------+---------+---------+---------+---------+
| 11       | 10      | 6       | 6       | 5       |
+----------+---------+---------+---------+---------+

Example 2 | Aggregate Function and 'GROUP BY' Clause

select grp1, 
       count(col1) as result1,
       count(col2) as result2
             from  (select 'a' as ord1, 'g1' as grp1, '10' as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, '40' as col1, 40 as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, '60' as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, '50' as col1, 50 as col2
                    union all
                    select NULL as ord1, NULL as grp1, NULL as col1, NULL as col2)
        group by grp1;

+----------+---------+---------+
|   GRP1   | RESULT1 | RESULT2 |
+----------+---------+---------+
| NULL     | 0       | 0       |
| g1       | 3       | 3       |
| g3       | 4       | 2       |
| g2       | 3       | 1       |
+----------+---------+---------+

Example 3 | Window Function

select  grp1, 
        ord1, 
        col1,
        count(col1) OVER(PARTITION BY grp1) as result1,
        count(col1) OVER(PARTITION BY grp1 ORDER BY ord1 ASC) as result2,
        count(col1) OVER(PARTITION BY grp1 ORDER BY ord1 DESC) as result3
             from  (select 'a' as ord1, 'g1' as grp1, '10' as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, '40' as col1, 40 as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, '60' as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, '50' as col1, 50 as col2
                    union all
                    select NULL as ord1, NULL as grp1, NULL as col1, NULL as col2);

+------+------+------+---------+---------+---------+
| GRP1 | ORD1 | COL1 | RESULT1 | RESULT2 | RESULT3 |
+------+------+------+---------+---------+---------+
| g1   | a    | 10   | 3       | 1       | 3       |
| g1   | b    | 20   | 3       | 2       | 2       |
| g1   | c    | 20   | 3       | 3       | 1       |
| g2   | a    | 30   | 3       | 1       | 3       |
| g2   | b    | 30   | 3       | 2       | 2       |
| g2   | c    | 40   | 3       | 3       | 1       |
| g3   | a    | 50   | 4       | 1       | 4       |
| g3   | b    | 50   | 4       | 2       | 3       |
| g3   | c    | 60   | 4       | 3       | 2       |
| g3   | d    | 50   | 4       | 4       | 1       |
| NULL | NULL | NULL | 0       | 0       | 0       |
+------+------+------+---------+---------+---------+