Thread: aggregate question

I know this one is simple enough.

I have two tables: packages and package_log.

paulsonsoft=# \d packages                                  Table "public.packages"    Column      |  Type   |
                Modifiers
 
-----------------+---------+------------------------------------------------------------------package_name    | text
|not nullpackage_desc    | text    | not nullpackage_ver     | text    | not nullpackage_date    | text    | not
nullpackage_loc    | text    | not nullpackage_type    | text    | not nullpackage_creator | text    |package_status  |
boolean| default truepackage_id      | integer | not null default
 
nextval('public.packages_package_id_seq'::text)
Indexes: packages_pkey primary key btree (package_id)
Foreign Key constraints: $1 FOREIGN KEY (package_type) REFERENCES
package_types(package_type) ON UPDATE CASCADE ON DELETE RESTRICT

paulsonsoft=# \d package_log                              Table "public.package_log"  Column    |  Type   |
              Modifiers
 
-------------+---------+-----------------------------------------------------------------custno      | text    | not
nullpackage_id | text    |timestamp   | text    | not nullip_address  | text    | not nullcompleted   | boolean
|current_ver| text    |logo        | text    |licenses    | text    |log_id      | integer | not null default
 
nextval('public.package_log_log_id_seq'::text)
Foreign Key constraints: $1 FOREIGN KEY (package_id) REFERENCES
packages(package_id) ON UPDATE CASCADE ON DELETE RESTRICT

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

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

On Mon, 23 Jun 2003 13:59:52 -0500 (CDT)
"Ryan" <pgsql-sql@seahat.com> wrote:

>  package_id      | integer | not null default
> 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.

if you would just package_id and count it would be simple:
selectp.package_id,count(*) as count
frompackages pleft outer join package_log l on p.package_id = l.package_id
;

but, if you want all fields from packages it get trickier.
you can do it in two ways:

selectp.*,(select count(*) from package_log l where l.package_id = p.package_id)
frompackages p

or:

selectp.*,coalesce(c.count,0)
frompackages pleft outer join (select l.package_id, count(*) as count from package_log l) c on p.package_id =
c.package_id


should work - but i just wrote it "by hand", and didn't test it. anyway - it should give either working code or idea on
howto achieve it.
 

depesz

Dnia 2003-06-24 00:04, Użytkownik Tomasz Myrta napisał:
>

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

> 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);

Add "group by package_name" to these queries...
Tomasz