hi all,

____________________
select hspectacle.hnum, count(*)

from
hspectacle left outer join v_jprix
on( hspectacle.hnum=v_jprix.hnum )

group by hspectacle.hnum
____________________________

gives
hnum | count(*)
1232 2
1564 8
461 7
....

But i want also the rows with count(*) = 0

How to do it???
thx

On 2 Oct, 11:30, Séverin Richard <severin.rich...@free.fr> wrote:
> hi all,
>
> ____________________
> select hspectacle.hnum, count(*)
>
> from
> hspectacle left outer join v_jprix
> on( hspectacle.hnum=v_jprix.hnum )
>
> group by hspectacle.hnum
> ____________________________
>
> gives
> hnum | count(*)
> 1232 2
> 1564 8
> 461 7
> ...
>
> But i want also the rows with count(*) = 0
>
> How to do it???
> thx

There cannot be a row with count = 0 as that implies that there are no
rows to count???

Indeed, the join seems redundant in this query as the grouping is done
on only the first table. So your query is equivalent to:

SELECT
hspectacle.hnum,
count(*)
FROM hspectacle
GROUP BY hspectacle.hnum

On Tue, 02 Oct 2007 13:50:40 +0200, Captain Paralytic
<paul_lautman@yahoo.com> wrote:

> On 2 Oct, 11:30, Séverin Richard <severin.rich...@free.fr> wrote:
>> hi all,
>>
>> ____________________
>> select hspectacle.hnum, count(*)
>>
>> from
>> hspectacle left outer join v_jprix
>> on( hspectacle.hnum=v_jprix.hnum )
>>
>> group by hspectacle.hnum
>> ____________________________
>>
>> gives
>> hnum | count(*)
>> 1232 2
>> 1564 8
>> 461 7
>> ...
>>
>> But i want also the rows with count(*) = 0
>>
>> How to do it???
>> thx
>
> There cannot be a row with count = 0 as that implies that there are no
> rows to count???
>
> Indeed, the join seems redundant in this query as the grouping is done
> on only the first table. So your query is equivalent to:
>
> SELECT
> hspectacle.hnum,
> count(*)
> FROM hspectacle
> GROUP BY hspectacle.hnum

And what he meant was to have something like:

SELECT
hspectacle.hnum,
count(vjprix.*)
FROM hspectacle
LEFT JOIN vj_prix
ON hspectacle.hnum=v_jprix.hnum
GROUP BY hspectacle.hnum
--
Rik Wasmus