Home > Enterprise >  SQL JOIN two tables and last column COUNT total occurences (by year, zip)
SQL JOIN two tables and last column COUNT total occurences (by year, zip)

Time:07-03

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
  • Related