CODEWITHSUNDEEP
sqlsql-servert-sqljoin
John
John

Reputation: 11

Help with SQL Server Join

I need some assistance with a join I am trying to do in SQL Server to create a view with a breakdown of customers by state in my database.

Table1: 

CustomerID, Gender, CustomerType

Table2: 

CustomerID, State, City, Zipcode

I want a view at the end to have the following (example):

ID| State | Males | Females | PlatinumCustomer | GoldCustomer | SilverCustomer
1    FL      200     300           40                 200           260  
2    MO      200     400           40                 300           360

The values for my Genders are M and F, the values for my CustomerTypes are P, G and S. Any help would be appreciated thank you!

Upvotes: 1

Views: 111

Answers (2)

Raj More
Raj More

Reputation: 48058

Try this

With Table1 AS
(
    Select 1 as CustomerId, 'M' Gender, 'Platinum' as CustomerType
    UNION SELECT 2, 'M', 'Platinum'
    UNION SELECT 3, 'F', 'Gold'
    UNION SELECT 4, 'M', 'Gold'
    UNION SELECT 5, 'F', 'Gold'
    UNION SELECT 6, 'M', 'Silver'
    UNION SELECT 7, 'F', 'Silver'
    UNION SELECT 8, 'M', 'Silver'
    UNION SELECT 9, 'F', 'Silver'
),
Table2 AS
(
    SELECT 1 as CustomerId, 'FL' State, NULL City, NULL ZipCode
    UNION SELECT 2, 'MO' State, NULL City, NULL ZipCode
    UNION SELECT 3, 'MO' State, NULL City, NULL ZipCode
    UNION SELECT 4, 'FL' State, NULL City, NULL ZipCode
    UNION SELECT 5, 'FL' State, NULL City, NULL ZipCode
    UNION SELECT 6, 'FL' State, NULL City, NULL ZipCode
    UNION SELECT 7, 'MO' State, NULL City, NULL ZipCode
    UNION SELECT 8, 'MO' State, NULL City, NULL ZipCode
    UNION SELECT 9, 'MO' State, NULL City, NULL ZipCode
)
Select 
    Row_Number() Over (Order By State) Id,
    State, 
    Sum (Case When Gender = 'm' then 1 else 0 end) as Males,
    Sum (Case When Gender = 'f' then 1 else 0 end) as Females,
    Sum (Case When CustomerType = 'Platinum' then 1 else 0 end) as PlatinumCustomer,
    Sum (Case When CustomerType = 'Gold' then 1 else 0 end) as GoldCustomer,
    Sum (Case When CustomerType = 'Silver' then 1 else 0 end) as SilverCustomer
From Table1 
    Inner Join Table2 
    on Table1.CustomerId = Table2.CustomerId
Group By State

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270775

Here's a basic pivot table which should get you basically what you need. You have an ID column in your example, which cannot be directly reproduced from your dataset. It would have to be calculated.

SELECT 
  State,
  SUM(CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) AS Males,
  SUM(CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) AS Females,
  SUM(CASE WHEN CustomerType = 'P' THEN 1 ELSE 0 END) AS PlatinumCustomer,
  SUM(CASE WHEN CustomerType = 'G' THEN 1 ELSE 0 END) AS GoldCustomer,
  SUM(CASE WHEN CustomerType = 'S' THEN 1 ELSE 0 END) AS SilverCustomer
FROM Table1 JOIN Table2 ON Table1.CustomerID = Table2.CustomerID
GROUP BY State

Upvotes: 6

Related Questions