-2

How do I count the number of countries separately into different columns using queries in SQL?

All the countries in column:

   Countries   
   US   
   Spain   
   Germany   
   US   
   Mexico

The country totals to display as:

Mexico 1 USA 2 Spain 1 Germany 1

I'm trying to use a query like this:

SELECT COUNT(Country) AS 'Mexico', COUNT(Country) AS 'USA', COUNT(Country) AS 'Spain', COUNT(Country) AS 'Germany'
FROM Customers
WHERE Country='Mexico' AND Country='US' AND Country='Spain' AND Country='Germany';

The result displayed as:

Mexico 0 USA 0 Spain 0 Germany 0

Anyone help me out here?

TJH
  • 11
  • 3
  • Use condition aggregation. – Thom A Aug 15 '21 at 16:13
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Aug 15 '21 at 16:14

2 Answers2

1

Use conditional aggregation:

select sum(case when country = 'Mexico' then 1 else 0 end) as mexico,
       sum(case when country = 'USA' then 1 else 0 end) as usa,
       sum(case when country = 'Spain' then 1 else 0 end) as spain,
       sum(case when country = 'German' then 1 else 0 end) as german        
from Customers;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-1

You can try:

Select distinct (Countries) as Country_Names, count(Countries) as Count_of_countries from Customers
Thom A
  • 88,727
  • 11
  • 45
  • 75
Shu_qasar
  • 72
  • 1
  • 10
  • This won't work, due to the omission of `Countries` from the `GROUP BY`. `DISTINCT` isn't a function, it's an operator. Though using `DISTINCT` with aggregation is normally a sign of a error as well. – Thom A Aug 15 '21 at 16:46