Reputation: 3190
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 ?
Upvotes: 1
Views: 44
Reputation: 176324
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;
Upvotes: 3
Reputation: 146349
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
Upvotes: 0