Count and print unique values in column

[jimmiller96]

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

[hielo]

Try:

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

[jimmiller96]

Try:

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

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

[jimmiller96]

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

[Brillig]

WHERE goes before GROUP BY clause.