CODEWITHSUNDEEP
sql
Kastriot
Kastriot

Reputation: 27

Count unique values of actual year (not present in previous years)

I am new to SQL and I was hoping you can help me with a question. I have a table from which I need to count unique values of this year, if they do not exist in years before (Basically I want to know only new entries for 2016).

BP Name       Year
A             2013
B             2016
A             2014
A             2016
C             2012
C             2016

In this case it would count only as 1 (BP Name: B)

Thanks Kastriot

Upvotes: 0

Views: 146

Answers (5)

Titus Andrews
Titus Andrews

Reputation: 26

This should work:

"unique values this year" =Table Name BP Name and Year = Columns

 select t."BP Name", t."Year" from 
(select "BP Name", "Year" from "unique values this year" where year("Year")=2016) t  
left join (select "BP Name" from "unique values this year" where year("Year")!=2016 ) t1 on t."BP Name"=t1."BP Name" 
    where t1."BP Name" is null

Upvotes: 1

Tejash Raghav
Tejash Raghav

Reputation: 46

try this

select count(*)
from
(select Distinct bpname,year
 from tmpBP a 
where a.year=2016 
and not exists  (select 1 from tmpBP b where a.bpname = b.bpname and a.year > b.year
)
)x

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I would approach this with two levels of aggregation. The following does the count for all years:

select first_year, count(*)
from (select pbname, min(year) as first_year
      from t
      group by pbname
     ) t
group by first_year;

For just 2016:

select count(*)
from (select pbname, min(year) as first_year
      from t
      group by pbname
     ) t
where first_year = 2016;

Upvotes: 1

Mansoor
Mansoor

Reputation: 4192

SELECT COUNT(BPName),BPName,_Year FROM #Temp otr WHERE NOT EXISTS(SELECT 1 FROM #Temp inr WHERE inr.BPName = otr.BPName and inr._Year > otr._Year ) GROUP BY BPName,_Year

Upvotes: 0

Abdul Rehman Sayed
Abdul Rehman Sayed

Reputation: 6672

check with this.

select count(*) from table1 a 
where a.year=2016 
and not exists  (select 1 from table1 b where a.bpname = b.bpname and a.year > b.year)

Upvotes: 0

Related Questions