pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp on (sub)-select
4+ messages / 2 participants
[nested] [flat]
* Help on (sub)-select
@ 2002-12-20 10:21 Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Gary Stainburn @ 2002-12-20 10:21 UTC (permalink / raw)
To: pgsql-sql
Hi Folks,
I have two tables
roster holds the duties to be performed and the number of people required per
duty.
roster_staff holds the people allocated to perform that duty.
I'm trying to create a select that will tally the roster_staff and include it
with the roster details. I've managed to get it working for a specific day,
but I can't seem to manage to get it working generically.
here's the select I've got that works, along with the output:
nymr=# select r.*, s.tally from roster r,
nymr-# (select count(*) as tally from roster_staff where
nymr(# rsdate = '2002-01-01' and rsgid = 11 and rsgsid = 2) as s
nymr-# where rodate = '2002-01-01' and rogid = 11 and rogsid = 2;
rodate | rogid | rogsid | rorequired | rooptional | tally
------------+-------+--------+------------+------------+-------
2002-01-01 | 11 | 2 | 0 | 1 | 2
(1 row)
nymr=#
What I want to be able to do is select multiple rows and have the correct
tally appear for that row.
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help on (sub)-select
@ 2002-12-20 10:51 Philip Warner <pjw@rhyme.com.au>
parent: Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 2 replies; 4+ messages in thread
From: Philip Warner @ 2002-12-20 10:51 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
At 10:21 AM 20/12/2002 +0000, Gary Stainburn wrote:
>nymr=# select r.*, s.tally from roster r,
>nymr-# (select count(*) as tally from roster_staff where
>nymr(# rsdate = '2002-01-01' and rsgid = 11 and rsgsid = 2) as s
>nymr-# where rodate = '2002-01-01' and rogid = 11 and rogsid = 2;
> rodate | rogid | rogsid | rorequired | rooptional | tally
>------------+-------+--------+------------+------------+-------
> 2002-01-01 | 11 | 2 | 0 | 1 | 2
>(1 row)
Try something like:
select r.*, count(*) from roster r, roster_staff s
where rsdate = rodate and rsgid = rogid and rsgsid = rogid
group by r.*
or
select r.*, (select count(*) from roster_staff s
where rsdate = rodate and rsgid = rogid and rsgsid = rogid
) roster r
May not be exactly right, but you should get the idea
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help on (sub)-select
@ 2002-12-20 11:48 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Philip Warner <pjw@rhyme.com.au>
1 sibling, 0 replies; 4+ messages in thread
From: Gary Stainburn @ 2002-12-20 11:48 UTC (permalink / raw)
To: Philip Warner <pjw@rhyme.com.au>; pgsql-sql
On Friday 20 Dec 2002 10:51 am, Philip Warner wrote:
> At 10:21 AM 20/12/2002 +0000, Gary Stainburn wrote:
> >nymr=# select r.*, s.tally from roster r,
> >nymr-# (select count(*) as tally from roster_staff where
> >nymr(# rsdate = '2002-01-01' and rsgid = 11 and rsgsid = 2) as s
> >nymr-# where rodate = '2002-01-01' and rogid = 11 and rogsid = 2;
> > rodate | rogid | rogsid | rorequired | rooptional | tally
> >------------+-------+--------+------------+------------+-------
> > 2002-01-01 | 11 | 2 | 0 | 1 | 2
> >(1 row)
>
> Try something like:
>
> select r.*, count(*) from roster r, roster_staff s
> where rsdate = rodate and rsgid = rogid and rsgsid = rogid
> group by r.*
This one came up with a parser error near '*' but I don't understand it enough
to debug it.
>
> or
>
> select r.*, (select count(*) from roster_staff s
> where rsdate = rodate and rsgid = rogid and rsgsid = rogid
> ) roster r;
This one ran, but the count column had the same value in every row - the total
count for the table.
I've managed it using an intermediate view. I've also extended it to show
everything I need - see below. I'd still like to hear from anyone who could
tell me how I can do this without the intermediate view tho'
create table roster ( -- roster definition table - holding jobs to be done
rodate date not null,
rogid int4 references diagrams(gid), -- diagram
rogsid int4 references jobtypes(jid), -- jobtype
rorequired int4, -- essential staff
rooptional int4, -- optional staff
primary key (rodate, rogid, rogsid)
);
create table roster_staff ( -- people on the roster
rsdate date not null,
rsgid int4 references diagrams(gid), -- diagram
rsgsid int4 references jobtypes(jid), -- jobtype
rssid int4 references staff(sid), -- staff id.
constraint r2 foreign key (rsdate,rsgid,rsgsid) references roster
(rodate,rogid,rogsid)
);
create view roster_tally as
select rsdate, rsgid, rsgsid, count(*) as rocount
from roster_staff group by rsdate, rsgid, rsgsid;
create view roster_details as
select r.*, coalesce(t.rocount,0) as rocount, coalesce(a.rocount,0) as
roavail
from roster r
left outer join roster_tally t on r.rodate = t.rsdate and
r.rogid = t.rsgid and
r.rogsid = t.rsgsid
left outer join roster_tally a on r.rodate = a.rsdate and
a.rsgid is null and
r.rogsid = a.rsgsid;
nymr=# select * from roster_details where rocount < rorequired and roavail >
0;
rodate | rogid | rogsid | rorequired | rooptional | rocount | roavail
------------+-------+--------+------------+------------+---------+---------
2002-01-01 | 12 | 4 | 1 | 0 | 0 | 1
(1 row)
nymr=#
>
> May not be exactly right, but you should get the idea
>
>
> ----------------------------------------------------------------
> Philip Warner | __---_____
> Albatross Consulting Pty. Ltd. |----/ - \
> (A.B.N. 75 008 659 498) | /(@) ______---_
> Tel: (+61) 0500 83 82 81 | _________ \
> Fax: (+61) 03 5330 3172 | ___________ |
> Http://www.rhyme.com.au | / \|
>
> | --________--
>
> PGP key available upon request, | /
> and from pgp5.ai.mit.edu:11371 |/
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Help on (sub)-select
@ 2002-12-20 15:53 Philip Warner <pjw@rhyme.com.au>
parent: Philip Warner <pjw@rhyme.com.au>
1 sibling, 0 replies; 4+ messages in thread
From: Philip Warner @ 2002-12-20 15:53 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
At 11:48 AM 20/12/2002 +0000, Gary Stainburn wrote:
> > select r.*, count(*) from roster r, roster_staff s
> > where rsdate = rodate and rsgid = rogid and rsgsid = rogid
> > group by r.*
>
>This one came up with a parser error near '*' but I don't understand it
>enough
>to debug it.
Replace the two occurrences of r.* with the list of fields in r that you
want to group the count by.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-12-20 15:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-20 10:21 Help on (sub)-select Gary Stainburn <gary.stainburn@ringways.co.uk>
2002-12-20 10:51 ` Philip Warner <pjw@rhyme.com.au>
2002-12-20 11:48 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2002-12-20 15:53 ` Philip Warner <pjw@rhyme.com.au>
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