Re: SQl Query Group By function

<Rajisistla_at_gmail.com> a écrit dans le message de news: 1173375715.123140.132370_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 Received on Thu Mar 08 2007 - 12:34:33 CST