razorcode7 razorcode7 - 6 months ago 8
SQL Question

various columns in sql

I'm trying to write an SQL statement to create a list of countries with the number of normal customers and corporate customers. So it should have 3 columns named, Country, Normal customer, and corporate customer. Country column includes all countries from the customer table. The Normal and corporate customer display the number of normal and the number of corporate customers respectively in each country.

The customer table contains – CustomerID, company, country

Should look like

Country | Normal Customer | Corporate Customer
----------------------------------------------------------------

Japan | 25 | 2
Korea | 23 | 0


so far I have only got this but I just need to know If I'm sort of going in the right track, it's not complete yet.

SELECT Country , company
COUNT
FROM Customer WHERE Company IS NOT NULL

Answer

I'm assuming that when there is no company present that it's a 'normal' customer here;

SELECT
Country
,SUM(CASE WHEN Company IS NULL THEN 1 ELSE 0 END) Normal_Customer
,SUM(CASE WHEN Company IS NOT NULL THEN 1 ELSE 0 END) Corporate_Customer
FROM Customer
GROUP BY Country