pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedaggregate question
4+ messages / 3 participants
[nested] [flat]
* aggregate question
@ 2003-06-23 18:59 Ryan <pgsql-sql@seahat.com>
0 siblings, 2 replies; 4+ messages in thread
From: Ryan @ 2003-06-23 18:59 UTC (permalink / raw)
To: pgsql-sql
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 null
package_desc | text | not null
package_ver | text | not null
package_date | text | not null
package_loc | text | not null
package_type | text | not null
package_creator | text |
package_status | boolean | default true
package_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 null
package_id | text |
timestamp | text | not null
ip_address | text | not null
completed | 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: aggregate question
@ 2003-06-23 22:04 Tomasz Myrta <jasiek@klaster.net>
parent: Ryan <pgsql-sql@seahat.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tomasz Myrta @ 2003-06-23 22:04 UTC (permalink / raw)
To: Ryan <pgsql-sql@seahat.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: aggregate question
@ 2003-06-24 07:33 Hubert Lubaczewski <hubert.lubaczewski@eo.pl>
parent: Ryan <pgsql-sql@seahat.com>
1 sibling, 0 replies; 4+ messages in thread
From: Hubert Lubaczewski @ 2003-06-24 07:33 UTC (permalink / raw)
To: Ryan <pgsql-sql@seahat.com>; +Cc: pgsql-sql
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:
select
p.package_id,
count(*) as count
from
packages p
left 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:
select
p.*,
(select count(*) from package_log l where l.package_id = p.package_id)
from
packages p
or:
select
p.*,
coalesce(c.count,0)
from
packages p
left 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 how to achieve it.
depesz
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: aggregate question
@ 2003-06-24 08:01 Tomasz Myrta <jasiek@klaster.net>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 4+ messages in thread
From: Tomasz Myrta @ 2003-06-24 08:01 UTC (permalink / raw)
To: pgsql-sql; +Cc: Ryan <pgsql-sql@seahat.com>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-06-24 08:01 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-23 18:59 aggregate question Ryan <pgsql-sql@seahat.com>
2003-06-23 22:04 ` Tomasz Myrta <jasiek@klaster.net>
2003-06-24 08:01 ` Tomasz Myrta <jasiek@klaster.net>
2003-06-24 07:33 ` Hubert Lubaczewski <hubert.lubaczewski@eo.pl>
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