RE: FN-FORUM: Sql query help

date posted 14th January 2004 12:16

Ron, quoting me:
> >Give us some sample data, and the results you expect to achieve.
>
> I want to display each supplier on the database in a table
> In addition I want to display the number of items for that
> supplier and the number of those items where the barcode field has
> been omitted
>
> If a supplier has 0 items or no missing barcodes they still get
> shown in the list
> ...
> So item 123 for Supplier1 has a null entry for barcode
> Supplier 2 has no items, all the other suppliers items all have barcodes
>
> I want:
>
> Name Address1 NumItems missiogBarcodes
> sup1 bora bora 56 1
> sup2 tenby 0 0
> sup3 peru 175 0
> sup4 peru 458 0
> sup5 peru 321 0
> etc etc

Ah, easy. The trick you're missing is that count(foo) doesn't count rows
where foo is NULL.
----------------------------8