Oracle count my data used

Question

Say I have a table like this

ID, SN, USED

002, 183000153206, 0
002, 180000205206, 0
002, 188000373206, 0
002, 186000495206, 0
002, 181000521206, 0

001, 180000107106, 0
001, 181000206106, 0
001, 182000337106, 0
001, 180000453106, 1
001, 188000557106, 0
001, 184000639106, 0

I would like to count SN used count for each ID, if my SQL script is right, the result could be :

ID, USED_Count
002, 0
001, 1

Here's what I did so far, but it only shows ID 001 record, no ID 002.

select   id, count(sn) as sums
from     sn_table
where    sn is not null
and      used is not null
group by id
--having   sums >= 0

I know the condition used is not null seems not right, but I just stocked here for hours, how can I done this ?

Answer 1

You could use simple SUM:

select   id, SUM(used) as sums
from     sn_table
group by id
order by id desc;

or conditional aggregation:

SELECT id, COUNT(DISTINCT CASE WHEN used = 1 THEN sn END) AS USED_COUNT
FROM sn_table
GROUP BY id;

DBFiddle Demo

Answer 2

It's not clear what logic you're trying to implement. maybe you just want to count distinct occurrences of each column?

select   id
         , count(distinct sn) as sn_cnt
         , count(distinct used) as used_cnt
from     sn_table
where    sn is not null
and      used is not null
group by id