-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. – Larnu 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,198,228
  • 53
  • 572
  • 709
-1

You can try:

Select distinct (Countries) as Country_Names, count(Countries) as Count_of_countries from Customers
Larnu
  • 76,706
  • 10
  • 34
  • 63
Shu_qasar
  • 72
  • 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. – Larnu Aug 15 '21 at 16:46