From: Tomasz Myrta <jasiek@klaster.net>
To: Ryan <pgsql-sql@seahat.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: aggregate question
Date: Tue, 24 Jun 2003 00:04:42 +0200
Message-ID: <3EF7797A.4040003@klaster.net> (raw)
In-Reply-To: <54717.65.102.128.233.1056394792.squirrel@fordparts.com>
References: <54717.65.102.128.233.1056394792.squirrel@fordparts.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, pgsql-sql@seahat.com
Subject: Re: aggregate question
In-Reply-To: <3EF7797A.4040003@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox