CODEWITHSUNDEEP
sql-servercount
Ralf Sigfridsson
Ralf Sigfridsson

Reputation: 11

I'm trying to count how many times data repeats in a table in SQL

I have a database that stores customer information in 2 tables.

Table stores

(tbl.contacts)

| Companyname | CountryID

and the second table (tbl_geo_country)

| ID | Countrycode | Name |

Now I want to create a report that can show me how many customers are from what country. example output

 | Country  | QNT |
   Norway      5
   USA         3
   Sweden      2

I dont know how many different countries it has stored so it also needs to check that.

Upvotes: 1

Views: 471

Answers (1)

dotnetom
dotnetom

Reputation: 24916

Seems like a JOIN and GROUP BY to me:

SELECT country.Name, COUNT(contact.ID) as QNT
FROM tbl_geo_country country 
    INNER JOIN tbl.contacts contact ON country.ID = contact.CountryID
GROUP BY country.Name
ORDER BY COUNT(contact.ID)

Keep in mind that this would return only countries, that have at least one contact. If you also need countries that have no contacts, you need to change INNER JOIN to LEFT JOIN.

Upvotes: 1

Related Questions