Count Subquery

[Eleven]

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!

[amitpatel66]

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.

[Eleven]

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

SELECT     dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID AS [Dallas Number], dbo.Canisters.CanistersTTNum, 
                      dbo.CanisterTracking.TrackingDateTime
FROM         dbo.CanisterTracking INNER JOIN
                      dbo.Terminals ON dbo.CanisterTracking.fkTerminalID = dbo.Terminals.fkTerminalKey INNER JOIN
                      dbo.Canisters ON dbo.Canisters.fkBankKey = dbo.Terminals.fkBankId
GROUP BY dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID, dbo.Canisters.CanistersTTNum, dbo.CanisterTracking.TrackingDateTime
 

Let me know if I left anything out.

Thanks!

[amitpatel66]

I have 3 tables, CanisterTracking, Terminals, and Canisters.

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

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

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

SELECT     dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID AS [Dallas Number], dbo.Canisters.CanistersTTNum, 
                      dbo.CanisterTracking.TrackingDateTime
FROM         dbo.CanisterTracking INNER JOIN
                      dbo.Terminals ON dbo.CanisterTracking.fkTerminalID = dbo.Terminals.fkTerminalKey INNER JOIN
                      dbo.Canisters ON dbo.Canisters.fkBankKey = dbo.Terminals.fkBankId
GROUP BY dbo.Terminals.TerminalId, dbo.Canisters.fkCanisterLongID, dbo.Canisters.CanistersTTNum, dbo.CanisterTracking.TrackingDateTime
 

Let me know if I left anything out.

Thanks!

Try this:

 
SELECT CanisterID, CanisterTTNum, COUNT(TerminalId) FROM
(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)
GROUP BY CanisterID, CanisterTTNum
 
 

[Eleven]

Try this:

 
SELECT CanisterID, CanisterTTNum, COUNT(TerminalId) FROM
(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)
GROUP BY CanisterID, CanisterTTNum
 
 

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

SELECT     CanisterID, CanisterTTNum, COUNT(TerminalID) AS Count
FROM         (SELECT     c.fkCanisterLongID AS CanisterID, c.CanistersTTNum AS CanisterTTNum, t.TerminalId AS TerminalID, ct.TrackingDateTime
                       FROM          dbo.Canisters AS c INNER JOIN
                                              dbo.Terminals AS t ON c.fkBankKey = t.fkBankId INNER JOIN
                                              dbo.CanisterTracking AS ct ON t.fkTerminalKey = ct.fkTerminalID) AS derivedtbl_1
GROUP BY CanisterID, CanisterTTNum
 

And I also need to have the date and the TerminalID on the results.

[amitpatel66]

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

SELECT     CanisterID, CanisterTTNum, COUNT(TerminalID) AS Count
FROM         (SELECT     c.fkCanisterLongID AS CanisterID, c.CanistersTTNum AS CanisterTTNum, t.TerminalId AS TerminalID, ct.TrackingDateTime
                       FROM          dbo.Canisters AS c INNER JOIN
                                              dbo.Terminals AS t ON c.fkBankKey = t.fkBankId INNER JOIN
                                              dbo.CanisterTracking AS ct ON t.fkTerminalKey = ct.fkTerminalID) AS derivedtbl_1
GROUP BY CanisterID, CanisterTTNum
 

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

[Eleven]

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.

[amitpatel66]

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:

 
SELECT c.canisterlongID, c. canisterTTNum,k.cnt,x.terminalid,x.trackingtime FROM 
(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
WHERE c.canisterlongID = k.fkcanisterID
AND k.fkcanisterID = x.fkcanisterID