SQL question about loops/cursors/counts (MySQL related)

Factual Questions
1

I’m running a MySQL server, and one of my databases has a table that includes a bunch of data about HIV samples that have been taken from patients around the world. One of the fields in the table is country_of_infection. I need to query the database and return each distinct country of infection, along with a count of how many records have that country as their country of infection. Obviously the first part is a simple SELECT DISTINCT country_of_infection FROM hiv_samples query, but what do I need to do to get the second part? Do I need to do something involving cursors, or break out a programming language, or am I way overthinking this?

2

I’m not familiar with MySQL but in SQL Server I would do something like


SELECT country_of_infection, count(*) FROM hiv_samples
GROUP BY country_of_infection

You should be able to do something similar.

3

Oh MAN, Shoeless, I forgot all about GROUP BY. Thanks for reminding me, I knew I was way overthinking it.

(As an aside, I did get it to work with cursors, after realizing I’d made a syntax error in the cursor name. :stuck_out_tongue: )

Thanks folks. Move along, nothing to see here… :wink: