Prev: Float Types Used as Primary Key on SQL 2005
Next: Inserting data into denormalized table
From: jj297 on 19 Jul 2010 11:14
TraineeInfo Table

TrainID Fname Lname
1 Barbara Johnson
1 Barbara Johnson


Training Table

TrainID Loc TrainDate
Times MaxRmSize
1 ClassA 2010-07-26 00:00:00.000 2:00 25
2 ClassB 2010-07-26 00:00:00.000 11:00 15
3 ClassC 2010-07-26 00:00:00.000 10:00 25


I wrote this and it gives me

select count(*) as SeatsTaken
from TraineeInfo
where TrainID='1'

Seats Taken = 2

How can I show SeatsLeft = 23?

From: SetonSoftware on 19 Jul 2010 13:59
On Jul 19, 11:14 am, jj297 <nchildress...(a)gmail.com> wrote:
> TraineeInfo Table
>
> TrainID      Fname       Lname
> 1       Barbara Johnson
> 1       Barbara Johnson
>
> Training Table
>
> TrainID      Loc           TrainDate
> Times        MaxRmSize
> 1       ClassA  2010-07-26 00:00:00.000 2:00    25
> 2       ClassB  2010-07-26 00:00:00.000 11:00   15
> 3       ClassC  2010-07-26 00:00:00.000 10:00   25
>
> I wrote this and it gives me
>
> select count(*) as SeatsTaken
> from TraineeInfo
> where TrainID='1'
>
> Seats Taken = 2
>
> How can I show SeatsLeft = 23?

You're just about there. JOIN TraineeInfo with Training and GROUP BY
TrainID. You can do MaxRmSize - COUNT(*) on the select line to get a
virtual column showing the remaining space for each TrainID.

BTW, when posting specific SQL questions its best to include the
CREATE TABLE/INSERT statements so the problem can be easily recreated.

Thanks

Carl
From: Eric Isaacs on 19 Jul 2010 14:07
Try:

SELECT
T.TrainID,
T.MaxRmSize,
COUNT(TI.TrainID) AS SeatsTaken,
T.MaxRmSize - COUNT(TI.TrainID) AS SeatsLeft
FROM
Training AS T LEFT JOIN TraineeInfo AS TI ON T.TrainID =
TI.TrainID
GROUP BY
T.TrainID

-Eric Isaacs
From: JJ297 on 20 Jul 2010 09:38
On Jul 19, 2:07 pm, Eric Isaacs <eisa...(a)gmail.com> wrote:
> Try:
>
> SELECT
>     T.TrainID,
>     T.MaxRmSize,
>     COUNT(TI.TrainID) AS SeatsTaken,
>     T.MaxRmSize - COUNT(TI.TrainID) AS SeatsLeft
> FROM
>     Training AS T LEFT JOIN TraineeInfo AS TI ON T.TrainID =
> TI.TrainID
> GROUP BY
>     T.TrainID
>
> -Eric Isaacs

Thanks Eric and Carl!!!!
From: --CELKO-- on 21 Jul 2010 01:03
"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

If you don't know anything about RDBMS, then get a copy of the
simplest intro book I know --
http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905

If you want a simple introduction to thinking in sets instead of
sequential file structures,look at:
http://sqluniversity.net/media.php?mfile=ThinkingInSets.rm&pid=57
 | 
Pages: 1
Prev: Float Types Used as Primary Key on SQL 2005
Next: Inserting data into denormalized table