Обсуждение: Help on (sub)-select

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     

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
groupby r.*
 

or
    select r.*, (select count(*) from roster_staff s    where rsdate = rodate and rsgid = rogid and rsgsid = rogid    )
rosterr
 

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   |/

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     

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   |/