By Alvin Alexander. Last updated: August 10, 2020
As a note to self, this is how I wrote a “group by” and “order by” SQL query that gives me the number of times each country code occurs in a database table named url_clicks
:
select country, count(1) as the_count from url_clicks where url_id=6 and country != '' group by country order by the_count desc
Results of this query look like this:
US, 50 CA, 30 GB, 15
That tells me that 50 people in the US clicked on the link with the id=6, 30 people from CA clicked on the same link, and 15 people from GB clicked on that link as well.
I can never remember the SQL syntax for a group-by, order-by query where I get the sorted count of the number of unique occurrences of each group-by field, so I’m putting this out here so I can remember the syntax in the future.