SQL cookbook: Counting and listing items in SQL

Photo by Deng Xiang on Unsplash

To build on last week’s post - How data skills make you a better PM - let’s do a bit of data analysis of my side project, Switch Scores. You can use the same approach for your own products.

Counting and listing items

I have a database table called games. This contains every Nintendo Switch game on Switch Scores. Each row in the table is a game.

A simple question to start with: How many games are in the database? I can answer this with the following SQL:

SELECT count(*) FROM games;

This gives a result of 15,600.

What if I wanted to see a list of all the games?

SELECT * FROM games;

That’s a lot of rows to scroll through. A more useful query is to just pull the 25 most recently released games. Then it would be:

SELECT * FROM games ORDER BY eu_release_date DESC LIMIT 25;

The ORDER BY sorts the games with the newest dates. DESC puts the newest dates first; ASC would put the oldest dates first. LIMIT 25 says I only want 25 items.

This isn’t just about Switch games. This same SQL pattern works any time you want to check what’s most recent in your product data: signups, transactions, or feedback entries.

There are a lot of columns in my games table. I only need to see the id, title, and date. SELECT * takes all the columns. Instead I can do this:

SELECT id, title, eu_release_date FROM games ORDER BY eu_release_date DESC LIMIT 25;

Would you find more posts like this useful - small SQL snippets explained in plain English? Leave a comment and let me know.

---

Subscribe below for more updates - I normally post once per week.