On Mar 8, 1:34 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <Rajisis..._at_gmail.com> a écrit dans le message de news: 1173375715.123140.132..._at_s48g2000cws.googlegroups.com...
> On Mar 8, 12:29 pm, lothar.armbrues..._at_t-online.de (Lothar Armbrüster)
> wrote:
>
>
>
>
>
> > Rajisis..._at_gmail.com writes:
> > > On Mar 8, 11:53 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> > >> <Rajisis..._at_gmail.com> a écrit dans le message de news: 1173372103.534945.216..._at_v33g2000cwv.googlegroups.com...
> > >> |i am trying to find out
> > >> | customer who has got more clicks and spent amount for yesterday
> > >> |
> > >> | select c.cust_key, max(c.bllble_clk_cnt) bllble_clk_cnt,
> > >> | max(bllble_clk_rev_amt) bllble_clk_rev_amt
> > >> |
> > >> | from pfp_acct_dly_fct c where
> > >> | time_key=TO_NUMBER(TO_CHAR(TRUNC(SYSDATE-1), 'YYYYMMDD'))
> > >> |
> > >> | group by c.cust_key
> > >> |
> > >> | But its giving more than one Rec. I need only one.
> > >> |
> > >> | I appreciate your help.
> > >> | Thanks
> > >> |
>
> > >> It gives you what you ask: 1 row per cust_key.
> > >> What do you want?
>
> > >> Regards
> > >> Michel Cadot
> > > I need
> > > only one Cust_Key ,but its giving so many
> > > customer who has got more clicks and spent amount for yesterday
>
> > Hello Rajisistla,
>
> > maybe this is what you want:
>
> > select
> > cust_key,
> > bllble_clk_cnt,
> > bllble_clk_rev_amt
> > from
> > (select
> > cust_key,
> > bllble_clk_cnt,
> > bllble_clk_rev_amt
> > from
> > pfp_acct_dly_fct
> > where
> > time_key=to_number(to_char(trunc(sysdate-1), 'YYYYMMDD'))
> > order by
> > bllble_clk_cnt desc,
> > bllble_clk_rev_amt desc)
> > where
> > rownum<2;
>
> > If you have more than one entry per cust_key, you could try this:
>
> > select
> > cust_key,
> > sum_bllble_clk_cnt,
> > sum_bllble_clk_rev_amt
> > from
> > (select
> > cust_key,
> > sum(bllble_clk_cnt) sum_bllble_clk_cnt,
> > sum(bllble_clk_rev_amt) sum_bllble_clk_rev_amt
> > from
> > pfp_acct_dly_fct
> > where
> > time_key=to_number(to_char(trunc(sysdate-1), 'YYYYMMDD'))
> > group by
> > cust_key
> > order by
> > sum_bllble_clk_cnt desc,
> > sum_bllble_clk_rev_amt desc)
> > where
> > rownum<2;
>
> > Hope that helps,
> > Lothar
>
> > --
> > Lothar Armbrüster | lothar.armbrues..._at_t-online.de
> > Hauptstr. 26 |
> > 65346 Eltville |- Hide quoted text -
>
> > - Show quoted text -
>
> Thank you Lothar Armbruster.
> I will try and let u know
> --------------------------------------------------
>
> It surely gives you only 1 row but does it give you the correct one?
> Till you answer to the question: "which one you want?"
> any answer is not correct.
>
> Regards
> Michel Cadot- Hide quoted text -
>
> - Show quoted text -
I want the Customer Key with max clicks
Received on Thu Mar 08 2007 - 12:43:55 CST
