0

Hi all,

Fairly new at SQL, and running into the following problem. I am trying to create a query to count the number of unique fields in a column and display, but can't seem to get it right. For example, each day there are x number of entries from y number of countries, and I want to display the result as such:

Country Count
CA 4
US 2
UK 7
DE 3

I am able to get the total count of unique values in the country column by using: SQL COUNT(DISTINCT country), but this doesn't help me count and order by country. Any thoughts, or a gentle push in the right direction would be greatly appreciated!

Thanks,
James

3
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by Brillig
0

Try:

SELECT Country, COUNT(*) as total FROM TABLENAME GROUP BY Country

Thanks for the example, it worked like a charm! Thanks

0

Quick follow-up on this question...

SELECT country, product, COUNT(country) as total FROM orders GROUP BY Country

works as expected, but trying to add a where clause produces SQL syntax errors. For example, the following will error out with syntax errors:

SELECT country, product, COUNT(country) as total FROM orders GROUP BY Country WHERE Dateonly=DATE(Now())

It almost seems as though the where clause is not allowed with this type of query, but I can't find any actual reference to the problem. Further thoughts?

Thanks

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.