{"id":743,"date":"2019-06-07T23:50:28","date_gmt":"2019-06-08T07:50:28","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=743"},"modified":"2020-04-11T06:58:14","modified_gmt":"2020-04-11T14:58:14","slug":"db2-count","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-aggregate-functions\/db2-count\/","title":{"rendered":"Db2 COUNT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the DB2 COUNT()<\/code> function to return the number of values in a set or the number of rows from a table.<\/p>\n\n\n\n

Introduction to Db2 COUNT() function<\/h2>\n\n\n\n

The Db2 COUNT()<\/code> function is an aggregate function<\/a> that returns the number of values in a set or the number of rows in a table.<\/p>\n\n\n\n

The following is the syntax of the COUNT()<\/code> function:<\/p>\n\n\n

COUNT( ALL | DISTINCT expression)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The COUNT()<\/code> function accepts a set of values which can be any built-in data type except for BLOB, CLOB, DBCLOB, and XML.<\/p>\n\n\n\n
The COUNT(expression)<\/code> is the same as COUNT(ALL expression)<\/code> which returns the number of non-null values in a set, including duplicates.<\/p>\n\n\n\n
The COUNT(DISTINCT expression)<\/code> returns the number of distinct non-null values.<\/p>\n\n\n\n
The COUNT(*)<\/code> returns the number of rows in a set, including rows that contain NULL<\/code> values.<\/p>\n\n\n\n
The COUNT()<\/code>  returns a result of INT<\/code> type. It never<\/em> returns NULL.<\/p>\n\n\n\n
The COUNT_BIG()<\/code> behaves the same as the COUNT()<\/code> function except for the type of return value that supports a larger range, i.e., BIGINT<\/a><\/code>.<\/p>\n\n\n\n
Db2 COUNT() function illustration<\/h2>\n\n\n\n
First, create a new table<\/a> named count_demo<\/code> that contains one integer column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> count_demo(c1 INT<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> count_demo(c1) \nVALUES<\/span>(1<\/span>),(2<\/span>),(3<\/span>),(NULL<\/span>),(3<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, view the data from the count_demo<\/code> table by using the following SELECT<\/code> statement:<\/p>\n\n\n
SELECT<\/span> c1 FROM<\/span> count_demo;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, this statement uses the COUNT(*)<\/code> function to return the number of rows from the count_demo<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n    COUNT<\/span>(*) row_count<\/span>\nFROM<\/span> \n    count_demo\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
ROW_COUNT   \n----------- <\/span>\n5 \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, this statement uses the COUNT(DISTINCT expression)<\/code> to get the number of non-null rows from the count_demo<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n    COUNT<\/span>(c1) result<\/span>\nFROM<\/span> \n    count_demo;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is the following:<\/p>\n\n\n
RESULT   \n----------- <\/span>\n4           \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, this statement uses the COUNT(DISTINCT expression)<\/code> to return distinct non-null values from the count_demo<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n    COUNT<\/span>(DISTINCT<\/span> c1) result<\/span>\nFROM<\/span> \n    count_demo;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The number of distinct non-null values in the count_demo<\/code> table is as follows:<\/p>\n\n\n
RESULT      \n----------- <\/span>\n3     \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 COUNT() examples<\/h2>\n\n\n\n
We’ll use the books<\/code> table from the sample database to demonstrate the COUNT()<\/code> function.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Using DB2 COUNT(*) function to return the number of rows from a table examples<\/h3>\n\n\n\n
This example uses the COUNT(*)<\/code> function to find the number of books from the books<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n    COUNT<\/span>(*)\nFROM<\/span>\n    books;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
BOOK_COUNT  \n----------- <\/span>\n1091     \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To get the number of books which have ISBN, you add a WHERE<\/code> clause as the following:<\/p>\n\n\n
SELECT<\/span>     \n    COUNT<\/span>(*) book_isbn\nFROM<\/span>\n    books\nWHERE<\/span> \n    isbn IS<\/span> NOT<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result:<\/p>\n\n\n
BOOK_ISBN   \n----------- <\/span>\n984 \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using DB2 COUNT() function with the GROUP BY clause example<\/h3>\n\n\n\n
The COUNT()<\/code> function is often used with the GROUP BY<\/code> clause to return the number of values for each group.<\/p>\n\n\n\n
This statement uses the COUNT(expression)<\/code> returns the number of books with ISBN for each publisher:<\/p>\n\n\n
SELECT<\/span> \n    p.name publisher, \n    COUNT<\/span>(isbn) book_with_isbn\nFROM<\/span> \n    books b\n    INNER<\/span> JOIN<\/span> publishers p\n        ON<\/span> p.publisher_id = b.publisher_id\nGROUP<\/span> BY<\/span> \n    p.name\nORDER<\/span> BY<\/span> \n    book_with_isbn DESC<\/span>;\t\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This picture illustrates the partial output:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
In this example, the GROUP BY<\/code> clause groups books by publishers and the COUNT(ISBN)<\/code> function returns the number of books with ISBN for every publisher.<\/p>\n\n\n\n
3) Using DB2 COUNT() function with the HAVING clause example<\/h3>\n\n\n\n
The following statement finds the publishers that have more than 30 books, where all the books have ISBN:<\/p>\n\n\n
SELECT<\/span> \n    p.name publisher, \n    COUNT<\/span>(isbn) book_with_isbn\nFROM<\/span> \n    books b\n    INNER<\/span> JOIN<\/span> publishers p\n        ON<\/span> p.publisher_id = b.publisher_id\nGROUP<\/span> BY<\/span> \n    p.name\nHAVING<\/span> \n    COUNT<\/span>(isbn) > 30<\/span>\nORDER<\/span> BY<\/span> \n    book_with_isbn DESC<\/span>;\t\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Db2<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the Db2 COUNT()<\/code> function to get the number of values in a set or the number of rows in a table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Second, insert five rows<\/a> into the count_demo<\/code> table:<\/p>\n\n\n
If the number of values in a set exceeds the maximum value of the INT<\/a><\/code> type, which is 2,147,483,647, you can use the COUNT_BIG()<\/code> function instead.<\/p>\n\n\n\n