Re: How can i force a 0 in a SQL query?

In article <01be49f4$9bb6d110$c4d14090_at_wspc0117>,   "Miguel Pinto" <j-miguel-pinto_at_telecom.pt> wrote:
> Hi experts,
>
> In a sql query like this:
>
> select group_client, count(*)
> from client_bills
> where bill like '1000%'
> group by group_client
>
> I just get in the output, the group_client that acomplish the condition
> bill like '1000%'.
> But what i need is to get all group_client and force a 0 to all other
> groups that don't acomplish the condition.
> I'v tried nvl() and decode() but nothing works.
>
> Some help will be greattly appreciated
> Thanks in advance
>
> Regards
> Miguel Pinto
>

If I understand your requirements correctly, it is something like this:

select group_client, count(*)
 from client_bills
 where bill like '1000%'
 group by group_client
union all
select group_client, 0
 from client_bills
 where bill not like '1000%'
 group by group_client, 2
;

not pretty since it forces two passes at the data. Is the bill column really text data? Are you really going for bills over 1000.00? There should be a better but I don't have it tonight. I'll try again in the AM.

But the above should work.

Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Jan 27 1999 - 23:19:51 CST