need total storage space for unique server

[weretiger]

[SOLVED] need total storage space for unique server

Hi,

Having 2 tables

tbl_Server

Server | ServerID
Server1 | S1
Server2 | S2


tbl_Database

Database | ServerID | StorageSize_GB
DB1 | S1 | 40
DB2 | S2 | 20
Oracle1 | S2 | 20
Oracle2 | S2 | 40
SQL1 | S1 | 40
SQL2 | S1 | 50


How can i have the total Storage Size of each server? in the end, result will be

Server | ServerID | TotalStorage_GB
Server1 | S1 | 140
Server2 | S2 | 80

 

[Galaxiom]

SELECT tbl_Server.Server, tbl_Server.ServerID, Sum(tbl_Database.StorageSize_GB) AS TotalStorage_GB
 FROM tbl_Server
 LEFT JOIN tbl_Database
 ON tblServer.ServerID = tbl_Database = ServerID
 GROUP BY tbl_Server.Server, tbl_Server.ServerID
 ORDER BY ServerID

BTW I used a Left Join so that any servers with no databses on them would be included and show zero total storage.