Re: Group by error!!!

From: Mark Anthony <mdejesus_at_gmail.com>
Date: 1 Sep 2004 10:07:37 -0700
Message-ID: <c95e8e39.0409010907.3f55bed@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

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 - 12:07:37 CDT