Re: Group by error!!!

From: Mark Anthony <mdejesus_at_gmail.com>
Date: 1 Sep 2004 10:07:37 -0700
Message-ID: <c95e8e39.0409010907.3f55bed_at_posting.google.com>

pratap_fin_at_rediffmail.com (Pratap) wrote in message news:<830861d2.0408312306.661a50c3_at_posting.google.com>...
> You should put the entire case statment for "Publisher" in the group by.
>
> Pratap

pratap_fin_at_rediffmail.com (Pratap) wrote in message news:<830861d2.0408312306.661a50c3_at_posting.google.com>...
> You should put the entire case statment for "Publisher" in the group by.
>
> Pratap

updated query.. but still doesn't work

i changed the case to a decode.

Select * FROM
(Select to_char(d.sale_dt,'MM') AS Month, to_char(d.sale_dt,'YY')AS Year,
 m.title_txt AS Title,
 DECODE (m.merch_type_cd, 'BOOK',( SELECT p.pub_nm FROM publisher p, book b

	 		   WHERE p.pub_id=b.pub_id AND b.ean_no=m.ean_no ),
                           'n/a') "Publisher",

 t.merch_type_desc AS Format,
 '$' || SUM(d.sale_qty * m.unit_price_amt) "Total Sales"  FROM daily_sales d, merchandise_type t, merchandise m  WHERE to_char(d.sale_dt,'MM') = '05' AND   to_char(d.sale_dt,'YY') = '98' AND
  m.ean_no=d.ean_no AND
  t.merch_type_cd=m.merch_type_cd
GROUP BY to_char(d.sale_dt,'MM'), to_char(d.sale_dt,'YY'),

         m.title_txt, t.merch_type_desc,
         DECODE ( m.merch_type_cd, 'BOOK',( SELECT p.pub_nm FROM
publisher p,
                  book b 		  				
		   WHERE p.pub_id=b.pub_id AND b.ean_no=m.ean_no ),'n/a')
                   ORDER BY SUM(d.sale_qty * m.unit_price_amt) desc

 )
WHERE ROWNUM <=25; Received on Wed Sep 01 2004 - 19:07:37 CEST