To count the number of different values that are stored in a given column, you simply need to designate the column you pass in to the COUNT function as DISTINCT . When given a column, COUNT returns the number of values in that column. Combining this with DISTINCT returns only the number of unique (and non-NULL) values.
Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows.
SELECT name,COUNT(*) as count
FROM tablename
GROUP BY name
ORDER BY count DESC;
What about something like this:
SELECT
name,
count(*) AS num
FROM
your_table
GROUP BY
name
ORDER BY
count(*)
DESC
You are selecting the name and the number of times it appears, but grouping by name so each name is selected only once.
Finally, you order by the number of times in DESCending order, to have the most frequently appearing users come first.
Just changed Amber's COUNT(*) to COUNT(1) for the better performance.
SELECT name, COUNT(1) as count
FROM tablename
GROUP BY name
ORDER BY count DESC;
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