CODEWITHSUNDEEP
sqlsql-server-2012
Vico
Vico

Reputation: 23

Compare results from last year and this year

I need some help with a query. I have a result set that looks like this:

Table Stucture: 

 GEO | YEAR | INS
 ---------------------------
 Ams | 2016 | something wrong1
 Ams | 2016 | something wrong2
 Ams | 2017 | something wrong1

I need to get a result set if in 2016 there was 'Something wrong1' for GEO 'AMS' and in 2017 we have again 'Something wrong1' for the same Geo 'AMS'. Show me the Geo 'AMS'

In essence I need to compare the results set of 2016 and the result set of 2017. If for the same Geo we have the same lack in 2016 and in 2017, the query needs to show me that GEO.

Any help would be appreciated

Query I got so far:

select Geo
from (
    select Geo
    from dbo.results
    WHERE (INS in ('something wrong1', 'something wrong2'))
) as res 
Group by Geo
having count(*) > 1

But this only shows me the Geo That are double it doesn't look at the INS column.

Upvotes: 1

Views: 116

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can do this using aggregation:

select Geo
from dbo.results
where ins = 'something wrong1' and
      year in (2016, 2017)
group by Geo
having count(distinct year) = 2;

I am not sure what 'something wrong2' has to do with the question.

If you just want the same ins in the two years, you can do:

select Geo, ins
from dbo.results
where year in (2016, 2017)
group by Geo, ins
having count(distinct year) = 2;

This returns both the Geo and ins.

Upvotes: 0

RealCheeseLord
RealCheeseLord

Reputation: 795

you should be able to do this with a selfjoin, something like:

SELECT Geo
  FROM dbo.results t1
  JOIN dbo.results t2
    ON t1.GEO = t2.GEO
   AND t1.INS = t2.INS
   AND t1.YEAR <> t2.YEAR
 GROUP BY Geo

Upvotes: 1

Related Questions