userid ssid  Fname  Status
1      101   1@        0
2      101   2@        1
3      101   3@        1
4      101   4@        2
5      102   5@        0

I want result like below

ssid, status_0_cnt, status_x_cnt
101    1             3    
102    1             0

I want results groupby ssid. But one count for Zero and another count for remaining status values.

quote:

---

Originally posted by madhivanan


select
ssid,
sum(case when status=0 then 1 else 0 end) as status_0_cnt,
sum(case when status>0 then 1 else 0 end) as status_x_cnt
from
your_table
group by ssid



Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by satish.gorijala

Thanks Madhavan. If i want to add Fname to output..how can i write query?

quote:

---

Originally posted by madhivanan


select
ssid,
sum(case when status=0 then 1 else 0 end) as status_0_cnt,
sum(case when status>0 then 1 else 0 end) as status_x_cnt
from
your_table
group by ssid



Madhivanan

Failing to plan is Planning to fail

---

developer :)

---

select 
ssid,
Fname,
sum(case when status=0 then 1 else 0 end) over (partition by ssid) as status_0_cnt,
sum(case when status>0 then 1 else 0 end) over (partition by ssid) as status_x_cnt
from your_table

quote:

---

Originally posted by Kristen

Hmmm ... I read it that Fname, being variable per ssid, and unique within the sample data, would need to be presented as comma delimited list for each ssid, or somesuch.

Are we taking bets?

---