CODEWITHSUNDEEP
sqlsql-server
Jay
Jay

Reputation: 11

Count every rows

i tried to count the value of every rows in MYSQL. But it only count the first row only. Can someone assist

First Query:

SELECT A, B, C
FROM [TEST].[dbo].[TEST3]

Result:

A   B   C
7   8   9
1   2   NULL
1   3   4
1   NULL    1

Count every rows but only the first row appear as result. Query

SELECT COUNT (A + B + C)
FROM [TEST].[dbo].[TEST3]

Result:

2

It supposed to be 7+8+9 = 22
1+2+NULL = 3

etc.

Upvotes: 0

Views: 63

Answers (4)

Amit Biswas
Amit Biswas

Reputation: 23

SELECT 
   A, B, C,
   Total = A + B+ C
FROM dbo.TEST

DO NOT USE COUNT.

Upvotes: 0

John Woo
John Woo

Reputation: 263693

COUNT() is a aggregate function which will return group result.

The result is actually correct since 1 + 2 + NULL = NULL, not 3.

SELECT COUNT (A + B + C) FROM [TEST].[dbo].[TEST3]

Returns 2 because COUNT() will count only non-null value. If you run the query without COUNT() it will return 4 rows.

SELECT A + B + C FROM [TEST].[dbo].[TEST3]

The result is 

24
NULL
8
NULL

However, if you wanted to return rows considering NULL as 0, you can use COALESCE within the columns,

SELECT COALESCE(A, 0) + COALESCE(B, 0) + COALESCE(C, 0) 
FROM [TEST].[dbo].[TEST3]

will now return

24
3
8
2

And when you write it with count, it will now return 4.

SELECT COUNT(COALESCE(A, 0) + COALESCE(B, 0) + COALESCE(C, 0) )
FROM [TEST].[dbo].[TEST3]

Result:

4

Here's a Demo.

Upvotes: 1

adrtam
adrtam

Reputation: 7211

This is what you want:

SELECT IFNULL(A,0)+IFNULL(B,0)+IFNULL(C,0)
FROM [TEST].[dbo].[TEST3]

You do not want to use COUNT() here. COUNT() is an aggregate function. It output once per group. In your case, the whole query will output only one value.

Moreover, adding NULL to anything will be NULL and COUNT() will ignore that. Therefore the output of your query is 2.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Just take the sum of the columns directly:

SELECT A, B, C,
    COALESCE(A, 0) + COALESCE(B, 0) COALESCE(C, 0) AS total
FROM [TEST].[dbo].[TEST3]

The reason why your current query using COUNT returns a single row is that COUNT is an aggregate function. In the absence of GROUP BY, you are telling SQL Server to return a count over the entire table.

Upvotes: 2

Related Questions