CODEWITHSUNDEEP
c#sqlsql-server
mark333...333...333
mark333...333...333

Reputation: 1360

SQL COUNT(Column) Counts also the null value which is wrong

I'm creating a total details in my project but I'm having a problem regarding the "COUNT" in my query. It also calculates the null. What I want is, count the column with values only other than that, the query will not count the null column.

Here's my code

SELECT COUNT(columnName) as TotalColumnValue FROM tblName

| columnName| 
|      value|
|           |
|      value|
|      value|

so the result count would be 3 and not 4 because the value is null.

Thank you in advance.

Upvotes: 1

Views: 1945

Answers (5)

sagi
sagi

Reputation: 40481

Actually, a IS NOT NULL condition like mentioned in all the answers is not necessary since count(columnName) will not count null values. So basically it enough to just filter empty string values and just specify the column in the count.

SELECT COUNT(columnName) as TotalColumnValue
FROM tblNam
WHERE columnName <> ''

Note -> to the question you've asked on the comments. <> means != or in words - NOT EQUAL

Upvotes: 6

Dylan Su
Dylan Su

Reputation: 6065

You need to exclude both NULL and empty string ''.

SELECT COUNT(columnName) as TotalColumnValue FROM tblName
WHERE columnName <> '';

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

If the value is really NULL, then COUNT should be excluding that row. Maybe it's not NULL but an empty string''. Try adding a NULLIF in your column:

CREATE TABLE tbl(columnName VARCHAR(10) NULL);
INSERT INTO tbl VALUES ('value'), ('value'), ('value'), ('');

SELECT COUNT(NULLIF(columnName, '')) FROM tbl;

Or you can filter for empty strings:

SELECT COUNT(columnName) FROM tbl WHERE columnNameIS NOT NULL AND columnName <> ''

Upvotes: 4

BVer
BVer

Reputation: 41

If you want the number of different values (not counting repeating values) use SELECT DISTINCT COUNT(columnName) AS TotalColumnValue FROM tblName WHERE columnName IS NOT NULL

Upvotes: 2

Ryan Riel
Ryan Riel

Reputation: 34

Adding where function solve your issue. :)

SELECT COUNT(columnName) as TotalColumnValue FROM tblName where columnName is not null

Upvotes: 2

Related Questions