My SQL - query to show the same house being sold in multiple years-CodePudding

I have a table of sold house prices loaded into My SQL, the output of which is shown below.

I want to run a query that will show all entries that were sold in 2020 and again in 2021

I consider and entry as a group of number,street,town,county,postcode

I don't really know where to begin!

Thanks

CodePudding user response：

show all entries that were sold in 2020 and again in 2021

SELECT number
FROM table
WHERE dateSold >= '2020-01-01' AND dateSold < '2022-01-01'
GROUP BY number
HAVING SUM(dateSold >= '2020-01-01' AND dateSold < '2021-01-01')
   AND SUM(dateSold >= '2021-01-01' AND dateSold < '2022-01-01')

CodePudding user response：

You can do this by filtering your table to all sales within the period required, then checking that the number of distinct years where a sale occurred is 2:

SELECT  t.Number, 
        t.Street, 
        t.Town, 
        t.County, 
        t.Postcode, 
        TotalSales = COUNT(*)
FROM    YourTableName AS t
WHERE   t.DateSold >= '20200101'                        -- Sold After 1st Jan 2020
AND     t.DateSold < '20220101'                         -- Sold before 1st Jan 2022
GROUP BY
        t.Number, t.Street, t.Town, t.County, t.Postcode
HAVING  COUNT(DISTINCT YEAR(t.DateSold)) = 2;           -- Sold in both years