CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Romart Dubluis
Romart Dubluis

Reputation: 5

SQL Server Count Specific Multiple Columns

I have this table:

+-----------+--------+
| FILE CODE | STATES |
+-----------+--------+
| TEST 1    | CA     |
| TEST 2    | CA     |
| TEST 1    | CA     |
| TEST 2    | CA     |
| TEST 3    | CA     |
| TEST 4    | CA     |
| TEST 5    | CA     |
| TEST 1    | AZ     |
| TEST 1    | LI     |
| TEST 4    | CA     |
| TEST 4    | CA     |
+-----------+--------+

THIS SHOULD BE THE OUTPUT (Please see image below it's counting but have some trouble on the OUTPUT)

+----------+--------+---------+
| FILECODE | STATES | COUNTS  |
+----------+--------+---------+
| TEST 1   |   CA   |       2 |
| TEST 1   |   AZ   |       1 |
| TEST 1   |   LI   |       1 |
| TEST 2   |   CA   |       2 |
| TEST 3   |   CA   |       1 |
| TEST 4   |   CA   |       3 |
| TEST 5   |   CA   |       1 |
+----------+--------+---------+

Code:

SELECT 
    S.States, S.FIRST_MORTGAGE_PRIMARY_LOAN_TYPE, C.COUNTS
FROM 
    Master_Files S 
INNER JOIN 
    (SELECT 
         States, FIRST_MORTGAGE_PRIMARY_LOAN_TYPE, 
         COUNT(FIRST_MORTGAGE_PRIMARY_LOAN_TYPE) as COUNTS  
     FROM 
         Master_Files 
     GROUP BY 
         States, FIRST_MORTGAGE_PRIMARY_LOAN_TYPE) C ON S.FIRST_MORTGAGE_PRIMARY_LOAN_TYPE = C.FIRST_MORTGAGE_PRIMARY_LOAN_TYPE 
                                                     AND S.States = C.States

enter image description here

Upvotes: 0

Views: 64

Answers (2)

Kashif Qureshi
Kashif Qureshi

Reputation: 1490

There is no reason to use a subquery here. This query should be enough:

SELECT states, 
       first_mortgage_primary_loan_type, 
       Count(first_mortgage_primary_loan_type) AS COUNTS 
FROM   master_files 
GROUP  BY states, first_mortgage_primary_loan_type

Upvotes: 2

saniales
saniales

Reputation: 461

Just to give you a hint: :)

SELECT FILECODE, STATES, COUNT(*) AS COUNT
FROM Table_Name
GROUP BY FILECODE, STATES

As the comment said, this is a basic GROUP BY problem. I advise you to make some exercises regarding AGGREGATE FUNCTIONS (AVG, MAX, MIN, COUNT etc...) and GROUP BY statements

Upvotes: 0

Related Questions