Re: SQl Query Group By function

From: Lothar Armbrüster <lothar.armbruester_at_t-online.de>
Date: Thu, 08 Mar 2007 18:29:12 +0100
Message-ID: <874povlk47.fsf@prometeus.none.local>

Rajisistla_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.armbruester_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |

Received on Thu Mar 08 2007 - 11:29:12 CST