Re: aggregate question

Dnia 2003-06-23 20:59, Użytkownik Ryan napisał:
> I know this one is simple enough.
> 
> I have two tables: packages and package_log.

<cut>

> I must be a total space case today because I can't hammer out the sql to
> get a listing of all the packages with a count() of the package_log by
> package_id.
> 
> Thanks,
> Ryan

select package_name, count(*) as n_packages
from packages join package_log using (package_id);

This query is the simplest one, but doesn't display packages without any logs.
If you need this, try this one:
select p.package_name, (select count(*) from package_log pl where pl.package_id=p.package_id)   as n_packages
from packages p;

And one more:
select package_name, sum(case when package_log.package_id is not null then 1 else 0 end)   as n_packages
from packages left join package_log using (package_id);


Regards,
Tomasz Myrta