CODEWITHSUNDEEP
sql-servert-sql
James Hay
James Hay

Reputation: 7325

Counting and combining rows

I have a table that is recording user locations from my web application by adding a country code to the 'countrycode' column of each row. Each row is representing a visit to a particular area.

So I have some data like

COL1    COL2    COL3    countrycode
asd     asd     asd        NZ
asd     asd     asd        NZ
asd     asd     asd        NZ
asd     asd     asd        US
asd     asd     asd        US

What I want to do is to query this table and show me something like this below

Country    Count
   NZ        3
   US        2

But I need it to be able to add a row for any more country codes that have come up. I cant get my head around a way to do this, I know I need to use the COUNT() function somehow...

Upvotes: 5

Views: 99

Answers (1)

Code Magician
Code Magician

Reputation: 24022

To get your example output, you can use GROUP BY and COUNT()

SELECT Country, COUNT(*)
FROM myTable
GROUP BY Country

COUNT(*) will count ever row and GROUP BY Country will split the results out by Country. The results will be dynamic based on the data in the table so you don't need to change your query if you add more records in the table with different countries.

See Group By in Books online

Upvotes: 6

Related Questions