Menu
Why can't we use count(distinct *) in SQL? As in to count all distinct rows?
581
The correct syntax for using COUNT(DISTINCT) is: SELECT COUNT(DISTINCT Column1) FROM Table; The distinct count will be based off the column in parenthesis. The result set should only be one row, an integer/number of the column you're counting distinct values of.
So, is there any difference? The simple answer is no – there is no difference at all. The COUNT(*) function counts the total rows in the table, including the NULL values.
COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.
Suppose we want to know the distinct values available in the table. We can use SQL COUNT DISTINCT to do so. In the following output, we get only 2 rows. SQL COUNT Distinct does not eliminate duplicate and NULL values from the result set.
select count(*) from (select distinct * from MyTable) as T
Although I strongly suggest that you re-think any queries that use DISTINCT. In a large percentage of cases, GROUP BY is more appropriate (and faster).
EDIT: Having read the question comments, I should point out that you should never ask the DBMS to do more work than actually needs doing to get a result. If you know in advance that there will not be any duplicated rows in a table, then don't use DISTINCT.
146
You can select all the columns in your table and group by...
SELECT column1, column2, column3, count(*)
FROM someTable
GROUP BY column1, column2, column3
why not?
select
count(distinct name)
from
people
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
