alternative solution to GROUP BY ALL clause [message #19146] |
Sun, 03 March 2002 00:38 |
prerna bhand
Messages: 2 Registered: March 2002
|
Junior Member |
|
|
hi
I need to replace GROUP BY ALL clause in my sql script which I'm using in SQL CE .As it doesn't support ALL
keyword,I need some alternative for that.
My query is
select prod_desc ,count(tbl_asa_tns_call_Detail.prod_id) as pri from tbl_Asa_prod_brnd, tbl_asa_tns_call_Detail where prod_position=1 and tbl_Asa_prod_brnd.prod_id=tbl_asa_tns_call_detail.prod_id
and call_id in(select call_id from tbl_asa_tns_call where
(day(call_date) between day('2001-01-01 00:00:00') and day
('2001-03-20 00:00:00')) and (month(call_date) between month('2001-01-02 00:00:00') and month('2001-03-20 00:00:00')) and (year(call_date) between year('2001-01-02 00:00:00') and year('2001-02-08 00:00:00'))) group by all prod_desc order by prod_desc
|
|
|
|
Re: alternative solution to GROUP BY ALL clause [message #19165 is a reply to message #19146] |
Mon, 04 March 2002 00:35 |
prerna bhand
Messages: 2 Registered: March 2002
|
Junior Member |
|
|
thanx,
but my prob is that i have to include even those prod_desc for which the where condition doesn't apply.
i.e. where prod_position=1.
I could have chosen left outer join or views also,but this also doesn't work in sql ce.
I need to display the info in follow format:
prod_desc count(prod_id)
a 0
b 0
c 1
d 2 etc.
Any more suggestions?
|
|
|