Re: GROUP BY QUESTION

From: <Srinivasulun_at_hotmail.com>
Date: Sat, 29 May 1999 00:08:37 GMT
Message-ID: <7inb64$d66$1@nnrp1.deja.com>

In article <7in35k$7qg$1_at_nnrp1.deja.com>,   yarch_at_hotmail.com wrote:
> Hi, I think there should be an easy answer for my problem???
>
> I have this data
>
> cust no cust prod dollar
> ------- --------- ---
> 1 A 10
> 1 B 10
> 1 C 10
>
> I need to do a sum of dollar based on cust no and i want to put
> something in the cust_prod column when i roll up, but if i include
> cust_prod in my select, then the GROUP BY requires all fields
> in the select to be in the GROUP BY....preventing it from rolling up.
> ultimately i would like the data to be
>
> Cust no cust prod dollar
> ------ --------- ------
> 1 A or B or C $30
>
> I just need to fill cust prod with one of the values...it doesn't
matter
> which one..
> the sql i used is..
>
> select
> cust_no,
> cust_prod,
> SUM(dollar)
> FROM ....
> WHERE CUST_NO = 1
> GROUP BY
> CUST_NO
>
> ***THIS DOESN'T WORK....I NEED TO INCLUDE CUST_PROD IN THE GROUP BY,
> but then it prevents it from rolling up..
>
> I would appreciate any ideas..
> Thanks,
> Mike
> yarch_at_hotmail.com
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

You can try this .. you need to apply some agrgation on columns not in GROUP BY

 SELECT

          cust_no,
          max(cust_prod),
          SUM(dollar)

 FROM ....
 WHERE CUST_NO = 1
 GROUP BY
 CUST_NO Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri May 28 1999 - 19:08:37 CDT