By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,398 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,398 IT Pros & Developers. It's quick & easy.

Count Subquery

Eleven
P: 19
Hi everybody,

I hope someone will be able to help me out, i'm quite new to databases.

I've got 3 tables, check the image below.

I've created a view, now I want to have another one that will use the current view to show all canisters that have been through more than one terminal. I know i need to have a COUNT somewhere but I'm not really sure how to go about doing it.
This new view should contain the TT number, Dallas number and DateTime of the last time they went through a terminal and which terminal it was.

Your help will be greatly appreciated!


Mar 5 '08 #1
Share this Question
Share on Google+
7 Replies


amitpatel66
Expert 100+
P: 2,367
Hi everybody,

I hope someone will be able to help me out, i'm quite new to databases.

I've got 3 tables, check the image below.

I've created a view, now I want to have another one that will use the current view to show all canisters that have been through more than one terminal. I know i need to have a COUNT somewhere but I'm not really sure how to go about doing it.
This new view should contain the TT number, Dallas number and DateTime of the last time they went through a terminal and which terminal it was.

Your help will be greatly appreciated!


Not able to view your image.
Anyways, could you provide the structure of your view for reference of our experts.
Mar 5 '08 #2

Eleven
P: 19
Not able to view your image.
Anyways, could you provide the structure of your view for reference of our experts.
I have 3 tables, CanisterTracking, Terminals, and Canisters.

CanisterTracking:
------------------------------
fkTerminalID
TrackingDateTime

Terminals:
----------------------
fkTerminalKey
TerminalId
fkBankId

Canisters
-------------------
fkCanisterLongId [Dallas Number]
CanistersTTNum
fkBankKey

Expand|Select|Wrap|Line Numbers
  1. SELECT     dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID AS [Dallas Number], dbo.Canisters.CanistersTTNum, 
  2.                       dbo.CanisterTracking.TrackingDateTime
  3. FROM         dbo.CanisterTracking INNER JOIN
  4.                       dbo.Terminals ON dbo.CanisterTracking.fkTerminalID = dbo.Terminals.fkTerminalKey INNER JOIN
  5.                       dbo.Canisters ON dbo.Canisters.fkBankKey = dbo.Terminals.fkBankId
  6. GROUP BY dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID, dbo.Canisters.CanistersTTNum, dbo.CanisterTracking.TrackingDateTime
  7.  
Let me know if I left anything out.

Thanks!
Mar 5 '08 #3

amitpatel66
Expert 100+
P: 2,367
I have 3 tables, CanisterTracking, Terminals, and Canisters.

CanisterTracking:
------------------------------
fkTerminalID
TrackingDateTime

Terminals:
----------------------
fkTerminalKey
TerminalId
fkBankId

Canisters
-------------------
fkCanisterLongId [Dallas Number]
CanistersTTNum
fkBankKey

Expand|Select|Wrap|Line Numbers
  1. SELECT     dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID AS [Dallas Number], dbo.Canisters.CanistersTTNum, 
  2.                       dbo.CanisterTracking.TrackingDateTime
  3. FROM         dbo.CanisterTracking INNER JOIN
  4.                       dbo.Terminals ON dbo.CanisterTracking.fkTerminalID = dbo.Terminals.fkTerminalKey INNER JOIN
  5.                       dbo.Canisters ON dbo.Canisters.fkBankKey = dbo.Terminals.fkBankId
  6. GROUP BY dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID, dbo.Canisters.CanistersTTNum, dbo.CanisterTracking.TrackingDateTime
  7.  
Let me know if I left anything out.

Thanks!

Try this:
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT CanisterID, CanisterTTNum, COUNT(TerminalId) FROM
  3. (SELECT c.fkCanisterLongId AS "CanisterID", c.CanistersTTNum AS "CanisterTTNum",t.TerminalId AS "TerminalID",ct.TrackingDateTime AS "TrackingDateTime" FROM TrackingDateTime ct, Terminals t, Canisters c WHERE t.terminalid = ct.fkTerminalID AND t.fkbankid = c.fkbankkey)
  4. GROUP BY CanisterID, CanisterTTNum
  5.  
  6.  
Mar 5 '08 #4

Eleven
P: 19
Try this:
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT CanisterID, CanisterTTNum, COUNT(TerminalId) FROM
  3. (SELECT c.fkCanisterLongId AS "CanisterID", c.CanistersTTNum AS "CanisterTTNum",t.TerminalId AS "TerminalID",ct.TrackingDateTime AS "TrackingDateTime" FROM TrackingDateTime ct, Terminals t, Canisters c WHERE t.terminalid = ct.fkTerminalID AND t.fkbankid = c.fkbankkey)
  4. GROUP BY CanisterID, CanisterTTNum
  5.  
  6.  
Thanks Amit..

Tried it and the count is 9814 for all the records, that can't be right.

I made a couple of changes to the code coz i kept getting errors
Expand|Select|Wrap|Line Numbers
  1. SELECT     CanisterID, CanisterTTNum, COUNT(TerminalID) AS Count
  2. FROM         (SELECT     c.fkCanisterLongID AS CanisterID, c.CanistersTTNum AS CanisterTTNum, t.TerminalId AS TerminalID, ct.TrackingDateTime
  3.                        FROM          dbo.Canisters AS c INNER JOIN
  4.                                               dbo.Terminals AS t ON c.fkBankKey = t.fkBankId INNER JOIN
  5.                                               dbo.CanisterTracking AS ct ON t.fkTerminalKey = ct.fkTerminalID) AS derivedtbl_1
  6. GROUP BY CanisterID, CanisterTTNum
  7.  
And I also need to have the date and the TerminalID on the results.
Mar 5 '08 #5

amitpatel66
Expert 100+
P: 2,367
Thanks Amit..

Tried it and the count is 9814 for all the records, that can't be right.

I made a couple of changes to the code coz i kept getting errors
Expand|Select|Wrap|Line Numbers
  1. SELECT     CanisterID, CanisterTTNum, COUNT(TerminalID) AS Count
  2. FROM         (SELECT     c.fkCanisterLongID AS CanisterID, c.CanistersTTNum AS CanisterTTNum, t.TerminalId AS TerminalID, ct.TrackingDateTime
  3.                        FROM          dbo.Canisters AS c INNER JOIN
  4.                                               dbo.Terminals AS t ON c.fkBankKey = t.fkBankId INNER JOIN
  5.                                               dbo.CanisterTracking AS ct ON t.fkTerminalKey = ct.fkTerminalID) AS derivedtbl_1
  6. GROUP BY CanisterID, CanisterTTNum
  7.  
And I also need to have the date and the TerminalID on the results.
I had a feeling that the query might not display correct results because there is no proper join with canister table. It is not correct to join using bankkey because same bankid can be assigned to more than one canister and the query will take wron count. One change that I would suggest you is to include canisterlongID in canisterTracking table so that we can use that column for joining. The table design is INCORRECT which is causing this problem
Mar 5 '08 #6

Eleven
P: 19
I had a feeling that the query might not display correct results because there is no proper join with canister table. It is not correct to join using bankkey because same bankid can be assigned to more than one canister and the query will take wron count. One change that I would suggest you is to include canisterlongID in canisterTracking table so that we can use that column for joining. The table design is INCORRECT which is causing this problem

There is already a fkCanisterId in the CanisterTracking table that matches the CanisterLongID in the Canisters table, i just didn't think about using that.
I didn't design the database, so i'm also noticing some things now.
Mar 5 '08 #7

amitpatel66
Expert 100+
P: 2,367
There is already a fkCanisterId in the CanisterTracking table that matches the CanisterLongID in the Canisters table, i just didn't think about using that.
I didn't design the database, so i'm also noticing some things now.
Now Here you go. This will make the job simpler.

Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT c.canisterlongID, c. canisterTTNum,k.cnt,x.terminalid,x.trackingtime FROM 
  3. (select fkcanisterid, COUNT(terminalid) cnt FROM canistertracking GROUP BY fkcanisterid) k,(select ct.fkcanisterid, ct.terminalid,ct.trackingtime FROM canistertracking ct, terminal t WHERE t.terminalid = ct.terminalid AND ct.trackingtime = (SELECT MAX(trackingtime) FROM canisertracking WHERE fkcanisterid = ct.fkcanisterid)) x, canister c
  4. WHERE c.canisterlongID = k.fkcanisterID
  5. AND k.fkcanisterID = x.fkcanisterID
  6.  
  7.  
Mar 5 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.