DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: For All SQL guru

  1. #1
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    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)

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    select buyer, count(*)
    from xyz
    group by buyer
    having count(*) > 1
    Jeff Hunter

  3. #3
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    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

  4. #4
    Join Date
    Jan 2002
    Posts
    25

    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
  •  


Click Here to Expand Forum to Full Width