CODEWITHSUNDEEP
sql-server
shane brennan
shane brennan

Reputation: 9

SQL Server : Combining Query Results

I have a program that stores different types "Assets" for buildings which can be marked as being "Removed". I can create a query to count the number of assets by type and another to count the number of items identified as still present. But what I want to do is combine the two into 1 table.

Query 1

SELECT
    theAssetOutletType, COUNT(id) AS TotalNoOfAssets
FROM
    dbo.tblLEGAssets
WHERE
    buildingID = 1
GROUP BY 
    theAssetOutletType

Query 2

SELECT
    theAssetOutletType, COUNT(id) AS ItemsStillPresent
FROM            
    dbo.tblLEGAssets
WHERE
    buildingID = 1 AND removed <> 0
GROUP BY 
    theAssetOutletType

Thank you in advance for any help

Upvotes: 0

Views: 44

Answers (4)

shane brennan
shane brennan

Reputation: 9

I finally found a solution... took a lot of trial and error but it now works. Here is my Stored Procedure Solution, with the additional table "tblLEGAssetOutletTypes" which contains a single fields with a list of the 6 asset types. The following code will always return 6 rows with the project type, the total number of assets, the number of assets removed and the total remaining. Hope someone else who needs a similar problem resolving an use the code:

SELECT tblLEGAssetOutletTypes.assetOutletType, nested.NoOfItems, nested.noRemoved, NoOfItems-noRemoved AS noLeft
FROM (SELECT        theAssetOutletType, COUNT(id) AS NoOfItems, SUM(removed) AS noRemoved
      FROM            tblLEGAssets
      where buildingID=@buildingID
     GROUP BY theAssetOutletType)  AS nested
RIGHT JOIN tblLEGAssetOutletTypes ON nested.theAssetOutletType = tblLEGAssetOutletTypes.assetOutletType;

Upvotes: 0

shane brennan
shane brennan

Reputation: 9

I've found a work around, by using a nested select - that I can use, but if it is still possible I'd love to know the answer:

SELECT theassetoutlettype,
       noofitems,
       noremoved,
       noofitems - noremoved AS noLeft
FROM   (SELECT theassetoutlettype,
               Count(id)    AS NoOfItems,
               Sum(removed) AS noRemoved
        FROM   dbo.tbllegassets
        WHERE  buildingid = 1
        GROUP  BY theassetoutlettype) nested

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would suggest conditional aggregation:

SELECT theAssetOutletType, 
       COUNT(*) as TotalNoOfAssets
       SUM(CASE WHEN removed <> 0 THEN 1 ELSE 0 END) as ItemsStillPresent
FROM dbo.tblLEGAssets 
WHERE buildingID = 1 
GROUP BY theAssetOutletType;

This puts the values in separate columns on the same row -- which makes more sense to me than on separate rows.

Upvotes: 1

Wranorn
Wranorn

Reputation: 798

Try Union:

SELECT theAssetOutletType, count(id) as TotalNoOfAssets FROM dbo.tblLEGAssets where buildingID=1 group by theAssetOutletType

UNION

SELECT theAssetOutletType, count(id) as ItemsStillPresent FROM dbo.tblLEGAssets where buildingID=1 and removed<> 0 group by theAssetOutletType

Upvotes: 0

Related Questions