SQL Server : JOIN two tables and last column COUNT total occurences (by year, zip)-CodePudding

I have a Housing and a Crime_Reports table in my SQL Server database. I am trying to count the total occurrences of crimes in the zipcode of a house was sold in with a single query.

There are many values in these tables, but the below is what I am joining on and matching by zip and year (the 'unique' ids between each table are totally different, the below is just an example).

My Housing table sample data:

uID	zipcode	saleyear	soldprice
1	12345	2018	300000
2	23345	2019	200000
3	56777	2018	500000
4	65789	2021	350000

My Crime Reports table sample data:

IncidentID	zipcode	occurredyear
1	12345	2018
2	23345	2019
3	56777	2018
4	65789	2020

I want to create a view table (from single query) with the crime occurrences counting the crimes at the house zip code that occurred that year:

uID	zipcode	saleyear	soldprice	Crime Occurrences At Zip
1	12345	2018	300000	23
2	23345	2019	200000	4
3	56777	2018	500000	50
4	65789	2021	350000	2

I tried the following, but this did not work:

SELECT 
    h.uID, h.zipcode, h.saleyear, 
    COUNT(c.IncidentID) AS Crime Occurrences At Zip
FROM 
    Housing h
INNER JOIN 
    Crime_Reports c ON h.zipcode = c.zipcode
                    AND h.saleyear = c.occurredyear
GROUP BY 
    1
ORDER BY 
    1

EDIT

Answers tested and posted below

CodePudding user response：

I think you can use Common Table Expressions (CTE) in this circumstances :

SQL Server Common Table Expressions (CTE)

 With CTE as (
    Select Count(IncidentID) AS Crime Occurrences, occurredyear, zipcode from Crime_Reports group by occurredyear, zipcode)
    
    
    select * from ( select * from Housing)tb1 left join (select  Crime Occurrences, occurredyear, zipcode from CTE) on tb1.zipcode = tb2.zipcode and tb1.saleyear = tb2.occurredyear

CodePudding user response：

UPDATE ANSWERS

SOLUTION 1 Thanks to @Charlieface (The problem was using 1 or 1,2,3 in BY Clauses)

 SELECT h.uID, h.zipcode, h.saleyear, COUNT(c.IncidentID) AS Crime Occurrences At Zip
   FROM Housing h
   INNER JOIN Crime_Reports c
            ON h.zipcode = c.zipcode
            AND h.saleyear = c.occurredyear
 GROUP BY h.uID, h.zipcode, h.saleyear
 ORDER BY h.uID, h.zipcode, h.saleyear

SOLUTION 2 Thanks to @Aiden Or

WITH CrimesCTE AS (
    SELECT COUNT(IncidentID) AS CrimeOccurrencesAtZip, occurredyear, zipcode 
    FROM Crime_Reports
    GROUP BY occurredyear, zipcode
)
    
SELECT * 
FROM 
    (SELECT * FROM Housing) h
    LEFT JOIN 
    (SELECT CrimeOccurrencesAtZip, occurredyear, zipcode FROM CrimesCTE) c
        ON h.zipcode = c.zipcode 
        AND h.saleyear = c.occurredyear