-
Hi!
My want to count only those buyer who appear more then 1 (> 1) in select stament.
What should be my sql statment.
select count(distinct(buyer)) from xxxx.
This will not work because it counts buyer who appear once.
(how can I ommit buyer who appear once)
-
select buyer, count(*)
from xyz
group by buyer
having count(*) > 1
Jeff Hunter
-
Thanks for reply, but I can not use it my query is summary (star query)
This is just one of the field.
select
td.time_dt Todate,
gd.ofr_grp_nm GroupName,
gd.ofr_grp_dt GroupDate,
max(ofr_close_dt)CloseDate,
count(distinct(it.item_sku)) ItemSKU,
sum(ofr_list_qty) ListQty,
sum(ofr_close_qty) CloseQty,
count(ofr_win_oid) NoOfWins,
sum(ofr_close_cost) CloseRev,
count(ofr_ord_oid) NoOfOrder,
sum(ofr_ord_tot_cost) OrderRevn,
decode(count(distinct(byr_key))
count(byr_key)/decode(count(distinct(byr_key)),0,1,count(distinct(byr_key))) RepeatBuyer,
sd.splr_usr_login Owner
from
dw.ofr_daily_tran dt, dw.ofr_grp_dim gd, dw.item_dim it, dw.time_dim td,dw.supplier_dim sd
where
dt.time_key = td.time_key and
dt.ofr_key = gd.ofr_key and
dt.item_key = it.item_key and
dt.splr_key = sd.splr_key and
sd.splr_id = 'b2c-vinod' and
td.time_dt >= to_date('1-Jan-2001','DD-MON-YYYY') and
td.time_dt <= to_date('1-Jan-2003','DD-MON-YYYY')
group by td.time_dt,gd.ofr_grp_nm, gd.ofr_grp_dt,sd.splr_usr_login
-
For ALL SQL guru
Originally posted by Kumud
Thanks for reply, but I can not use it my query is summary (star query)
This is just one of the field.
select
td.time_dt Todate,
gd.ofr_grp_nm GroupName,
gd.ofr_grp_dt GroupDate,
max(ofr_close_dt)CloseDate,
count(distinct(it.item_sku)) ItemSKU,
sum(ofr_list_qty) ListQty,
sum(ofr_close_qty) CloseQty,
count(ofr_win_oid) NoOfWins,
sum(ofr_close_cost) CloseRev,
count(ofr_ord_oid) NoOfOrder,
sum(ofr_ord_tot_cost) OrderRevn,
decode(count(distinct(byr_key))
count(byr_key)/decode(count(distinct(byr_key)),0,1,count(distinct(byr_key))) RepeatBuyer,
sd.splr_usr_login Owner,
COUNT(BUYER)
from
dw.ofr_daily_tran dt, dw.ofr_grp_dim gd, dw.item_dim it, dw.time_dim td,dw.supplier_dim sd
where
dt.time_key = td.time_key and
dt.ofr_key = gd.ofr_key and
dt.item_key = it.item_key and
dt.splr_key = sd.splr_key and
sd.splr_id = 'b2c-vinod' and
td.time_dt >= to_date('1-Jan-2001','DD-MON-YYYY') and
td.time_dt <= to_date('1-Jan-2003','DD-MON-YYYY')
group by td.time_dt,gd.ofr_grp_nm, gd.ofr_grp_dt,sd.splr_usr_login,BUYER
HAVING COUNT(BUYER) > 1
GVK
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|