CODEWITHSUNDEEP
sqlpostgresql
Arun Mohan
Arun Mohan

Reputation: 347

How to get selective count in sql

i have a intermediate result as follows

year    ID                  edition
1996    WOS:000074643400033 WOS.SCI
1996    WOS:000074643400033 WOS.ISSHP
1996    WOS:000074643400033 WOS.ISTP
2004    WOS:000222568300039 WOS.ISTP
2004    WOS:000222568300039 WOS.SCI
2008    WOS:000265048200175 WOS.ISTP
2009    WOS:000275179901182 WOS.ISTP
2009    WOS:000275179901182 WOS.ISSHP

now i must run a count on top of this result with the following conditions, if a ID contain both "WOS.ISTP" and "WOS.ISSHP" edition in a same year, it must be counted just once.

my final table should look like the following.

Note: i have added the "intermediate_count" column just for understanding purpose, it need not appear in the final table.

year    ID                  edition     intermediate_count  Final_count
1996    WOS:000074643400033 WOS.SCI       1                     2
1996    WOS:000074643400033 WOS.ISSHP     1 
1996    WOS:000074643400033 WOS.ISTP        

2004    WOS:000222568300039 WOS.ISTP      1                     2
2004    WOS:000222568300039 WOS.SCI       1 

2008    WOS:000265048200175 WOS.ISTP      1                     1

2009    WOS:000275179901182 WOS.ISTP      1                     1
2009    WOS:000275179901182 WOS.ISSHP

i tried use CASE in the following way but didn't work out.

select id, year, 
       case 
           when edition_code = 'WOS.ISTP' and edition_code = 'WOS.ISSHP' then 'both' 
           else edition_code
       end as edition_code_new   
from table
group by id, year, edition_code_new
order by id;

any help would be appreciated, thanks in advance.

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Assuming you want one row per id and year, you can use count(distinct):

select id, year, 
       count(distinct case when edition_code in ('WOS.ISTP', 'WOS.ISSHP')
                           then 'WOS.ISTP'
                           else edition_code
             end) as count
from table
group by id, year
order by id;

You can also phrase this as a window function if you want the original rows.

Upvotes: 2

Related Questions