I have a Housing and Crime_Reports tables on my sql database (sql management studio). 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 Example:
uID | zipcode | saleyear | soldprice |
---|---|---|---|
1 | 12345 | 2018 | 300000 |
2 | 23345 | 2019 | 200000 |
3 | 56777 | 2018 | 500000 |
4 | 65789 | 2021 | 350000 |
My Crime Reports Table Example:
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:
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
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