user2171512 user2171512 - 5 months ago 8
SQL Question

Return zeros in empty query result

I have this query:

SELECT
--DGC.Name
[GameChannel],
SUM([AdjustedGames] ) *100. / SUM(SUM([AdjustedGames])) OVER() [Percentage]
FROM #GameChannelResults1 GC
--LEFT OUTER JOIN [WarehouseMgmt].[DimGameChannel] DGC ON DGC.Name = GC.[GameChannel]
GROUP BY [GameChannel]--DGC.Name


But when there is no match it returns empty result(nothing).I want to put somehow all values from [WarehouseMgmt].[DimGameChannel].Name as GameChannel and 0 for percentage if there is no match or result is empty. How i can do that?

Answer

Maybe this, JOIN to your dimension, like you already had commented.

    SELECT 
        DGC.Name
        ISNULL(SUM([AdjustedGames] ) *100. / SUM(SUM([AdjustedGames])) OVER(),0)  [Percentage]
    FROM [WarehouseMgmt].[DimGameChannel] DGC
    LEFT JOIN #GameChannelResults1 GC ON DGC.Name = GC.[GameChannel]
    GROUP BY DGC.Name