Question

Write a SQL query that displays the number of customers from Mexico, USA, and Canada. Hint: The result of the SQL query should look like the following table: CustomerCountNorthAmerica 21

Answer 1

(Assuming) For this query to work, the Table named "Customers" must have atleast two columns: 'Customer_ID', 'Country'

If you are going specifically for Mexico, USA and Canada then your query should be like:

SELECT 'CustomerCount' AS CustomerCount, Country, COUNT(Customer_ID)
FROM Customers
WHERE Country = 'Mexico' OR Country = 'USA' OR Country = 'Canada'
GROUP BY Country;

If you want to get the customer for every country present in the table, use this query:

SELECT 'CustomerCount' AS CustomerCount, Country, COUNT(Customer_ID)
FROM Customers GROUP BY Country;

You can also append ORDER BY ColumnName to sort the result in order of ColumnName

Basically what we are doing here is, We are grouping the table rows by CountryName and then counting the Customer_ID which should be unique to find out the number of the customers.

'CustomerCount' AS CustomerCount This is used to create a column in the resulting table with all the values fixed as 'CustomerCount'. I have used this to match with the provided example.

If you want to give certain name (here i have used ColumnName) to the column

COUNT(Customer_ID)

then you can replace this with

COUNT(Customer_ID) AS ColumnName