CODEWITHSUNDEEP
sql-servercountconcatenation
rafacardosoc
rafacardosoc

Reputation: 251

Count number of distinct combinations across multiple columns in SQL

Multiple flavors of this question have been asked, but I was not able to find a twist to what I am trying to do. This is my original table (sqlServer): 

State  City           Job
CA     LOS ANGELES    DEVELOPER
CA     SAN FRANCISCO  DESIGNER
CA     LOS ANGELES    DESIGNER
NY     NEW YORK       DEVELOPER

I would like to count the distinct combinations of State and City and return it as a new column in the table. So the final product looks like: 

State  City           Job         # of Unique
CA     LOS ANGELES    DEVELOPER   3
CA     SAN FRANCISCO  DESIGNER    3
CA     LOS ANGELES    DESIGNER    3
NY     NEW YORK       DEVELOPER   3

I tried doing a inner join with the same table and using a count, but that is not returning correct results. Any help would be greatly appreciated.

Upvotes: 2

Views: 1651

Answers (1)

Rich Benner
Rich Benner

Reputation: 8113

Try this query;

Test Data

CREATE TABLE #TestData (State nvarchar(2), City nvarchar(20), Job nvarchar(20))
INSERT INTO #TestData
VALUES
 ('CA','LOS ANGELES','DEVELOPER')
,('CA','SAN FRANCISCO','DESIGNER')
,('CA','LOS ANGELES','DESIGNER')
,('NY','NEW YORK','DEVELOPER')

Query

SELECT
     a.State
    ,a.City
    ,a.Job
    ,b.CountField
FROM #TestData a
CROSS JOIN (SELECT 
            COUNT(DISTINCT State + City) CountField
            FROM #TestData
            ) b

Output (unordered because no order by statement)

State   City            Job         CountField
CA      LOS ANGELES     DEVELOPER   3
CA      LOS ANGELES     DESIGNER    3
CA      SAN FRANCISCO   DESIGNER    3
NY      NEW YORK        DEVELOPER   3

As per your question in the comments, you can do this to filter for only CA in the count;

SELECT
     a.State
    ,a.City
    ,a.Job
    ,b.CountField
FROM #TestData a
CROSS JOIN (SELECT 
            COUNT(DISTINCT State + City) CountField
            FROM #TestData
            WHERE State = 'CA'
            ) b

Which should output

State   City            Job         CountField
CA      LOS ANGELES     DEVELOPER   2
CA      LOS ANGELES     DESIGNER    2
CA      SAN FRANCISCO   DESIGNER    2
NY      NEW YORK        DEVELOPER   2

Upvotes: 4

Related Questions