When do we use COUNT() or SUM()?

Frequently Asked Questions SQL FAQ
1

Question

When do we use the COUNT() function or the SUM() function?

Answer

Although they might appear to perform a similar task, the COUNT() and SUM() functions have very different uses.

COUNT() is used to take a name of a column, and counts the number of non-empty values in that column. COUNT() does not take into account the actual values stored, and only cares if they have a non-empty value. Each row is essentially counted as 1 towards the total count.

On the other hand, SUM() takes a column name, and returns the sum of all values in the column, meaning that it must take into account the actual values stored.

In general, use COUNT() when you want to count how many rows contain a non-empty value for a specified column. Use SUM() when you want to get the total sum of all values in a column.

12 Likes
2

Hi!

If SUM () takes all the non-empty values from a column, how can you count the number of empty ones?
Thanks!

5 Likes
3

They answered that previously. You can use:

SELECT COUNT(column_name)
FROM table
WHERE column_name IS NULL;
19 Likes
4

COUNT() is used to take a name of a column, and counts the number of non-empty values in that column.
The above is wrong.
do you mean.
COUNT() is used to take a name of a ROW, and counts the number of non-empty values in that ROW.

5

COUNT(*) returns the number of rows that fulfill a specified condition (WHERE x or HAVING y) in a table.

COUNT(column_name) returns the number of rows in a table the value in which the particular named column is not Null. It is equivalent to

SELECT COUNT(*) FROM table 
WHERE condition 
AND column_name != NULL;
6

Does SUM() take number data-type only ?

3 Likes
7

SUM() takes the number and decimal data types. It ignores the string (text) if finds.

4 Likes
8

So if you want to count everything, you will add both conditions?
or is it better to do it separately?

SELECT COUNT(*) FROM table
WHERE condition
AND column_name != NULL AND column_name = NOT NULL;

10

You cannot cannot compare NULL with ( = , != ) operators . For comparing null there are special operator : IS NULL and IS NOT NULL .

More Details : https://www.codecademy.com/paths/web-development/tracks/sql-for-web-development/modules/webdev-sql-intro/lessons/queries/exercises/is-null

5 Likes
11

Here is an analogy of the use of count() and sum() functions .

count(*) or count(1) - count no of rows in a given table , including the NULL values . If it has a “where” clause it will only count the records with satisfied condition.

count(column_name) - count no of rows for the non-NULL values in the column specified in parenthesis.

SUM() - Returns sum of values in the specified column . To be used on sum scenarios and on numeric columns . It avoids NULL values .

3 Likes
12

@beta0888222597 also, your question is redundant. If you want to count everything (null and non-null) you would just get the full table. You can do that by just checking the attribute of the table and see how many rows there are.

13

@supriya.falle rows don’t have names. Each row is just an observation with an maybe a primary key id. As other have said count() looks at a column and counts the number of non-empty values in that column.

14