Say you have a SQL database table which has some type of repeating items, like suburbs in a table of contacts, or categories in a products list and you want to return a list of the unique or distinct items, and then count up how many of each repeated items is listed. I always forget how to do this, so I wrote this post as a reminder.
Firstly the SELECT DISTINCT option, this is a super easy way to return a list of all the unqiue items in a database column.
SELECT DISTINCT suburb FROM tablename
You could also do a similar thing using the GROUP BY function, which as shown below, goes at the end, instead of the start.
SELECT suburb FROM tablename GROUP BY suburb
Now, let’s do some counting. Simply add the count(*) into the SELECT column list and this will add a second column to your list which returns the amount of each unique value is listed in the database table.
SELECT suburb, count(*) FROM tablename GROUP BY suburb
You can use the above as a starting point and expand on it with things like WHERE to target specific table values, or the ORDER BY to display the results in a specific order.
I hope this helps out on someone’s project 🙂