Does COUNT() include duplicate values of a column?

Frequently Asked Questions SQL FAQ
1

Question

When using the SQL COUNT() function for a column, does it include duplicate values?

Answer

Yes, when using the COUNT() function on a column in SQL, it will include duplicate values by default. It essentially counts all rows for which there is a value in the column.

If you wanted to count only the unique values in a column, then you can utilize the DISTINCT clause within the COUNT() function.

Example

/* This will return 22, the number of distinct category values. */
SELECT COUNT(DISTINCT category)
FROM fake_apps;
24 Likes
2

if the cell have an undifined value it won t be counted ?

3 Likes
3

That is correct. The COUNT function will only count non-NULL values when a specific column is selected. To count all rows you need to used the wildcard ‘*’ value in the COUNT.

8 Likes
4

I have been struggling really hard to understand the below querry

SELECT account_id, aacount_name, orders, COUNT(*)
FROM ..........
JOIN.............
ON .............
GROUP BY account_id, account_name, orders
HAVING.........;

My major challenge is the application of COUNT() on the columns proceeding it. Please what exactly does the COUNT() aggregate function count in this case?

2 Likes
6

It should return the total number of rows for that combination of account_id, account_name and orders.

For example, if we had the following data:

Account_Id  Account_Name Orders 
----------  ------------ ------
5510        Jill         12
5510        Jill         12
6610        Joseph       13
6610        Joseph       50
6610        Joseph       20
2220        Ally          5
2220        Ally          6

We should get:

Account_Id  Account_Name Orders Count(*)
----------  ------------ ------ --------
5510        Jill         12     2
6610        Joseph       13     1
6610        Joseph       50     2
2220        Ally          5     1 
2220        Ally          6     1
5 Likes
7

Instead of selecting a specific column and using * for the entire column the statement doesn’t return any result :

/* This will not return anything */
 SELECT COUNT(DISTINCT *)
 FROM fake_apps;
1 Like
8

Using DISTINCT * means the whole row needs to be unique.

2 Likes
9

Yes I agree, But my question is why doesn’t it return any value ? If you can elaborate more it will be helpful.

10

Anyways Thank You @coffeencake
I found the answer on StackOverflow.
Anyone intrested can look into : https://stackoverflow.com/questions/5010470/why-doesnt-countdistinct-work#

3 Likes
11

Thanks for sharing a more detailed explanation.

1 Like
12

Why then in “6610 Joseph 50 2” the count is 2 and where did go “6610 Joseph 20” ?
Thank you.

13

Hello @xalava,

Good catch on that! The data should have read 6610 Joseph 50.

14

Did you mean:

We should get:

Account_Id  Account_Name Orders Count(*)
----------  ------------ ------ --------
5510        Jill         12     2
6610        Joseph       13     1
6610        Joseph       20     1
6610        Joseph       50     1
2220        Ally          5     1 
2220        Ally          6     1

?

15

Yes, with the data presented above, yes, that would be the result.

1 Like
16

Hi!
I wanted to know what is the logic for the order in each query.
In this particular example for COUNT rows, why we start with Select, then specify FROM and at the end the WHERE, can we do it in a different order? Otherwise its impossible to run the query…
Thanks :slight_smile:

18

Thank you! It is useful.

19

I too had the same doubt, thanks for sharing the link! : )

20

guys, what would happen if you put a string as the parameter inside the parentheses, in my case, based on the given course on max/min function, it returns still a string. yearfix.

select max(name) from fake_apps;

21

also this,

select category, price, avg(downloads)

from fake_apps

group by category, price

if you only put ‘group by price’
it displays a result but I don’t reallt understand

;

22

In your answer, How to get something like this

Account_Id Account_Name Orders Count(*)

5510 Jill 12 2
6610 Joseph 50 3
2220 Ally 5 2

???