user2171512 user2171512 - 6 months ago 22
SQL Question

Count Number of games per product type

How can I count

NumOfGames%
per product type like
% = TOTAL/PERPRODUT_TYPE
.
Here is my query for the
TOTAL NumOfGames


SELECT
DPT.[Name] [ProductType],
SUM([FinishedGameCycleCount]) [NumOfGames]
FROM [WarehouseMgmt].[FactGameAgr] FWA
JOIN [WarehouseMgmt].[DimPlayer] DPL ON FWA.[PlayerId] = DPL.[Id]
JOIN [WarehouseMgmt].[DimGame] DG ON FWA.[GameId] = DG.[Id]
JOIN [WarehouseMgmt].[DimProductType] DPT ON DPT.Id = FWA.ProductTypeId
WHERE [WarehouseMgmt].[GetDateTimeFromTimeId](TimeId) >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)
GROUP BY DPT.[Name]

Answer

Use SUM() OVER()

SELECT     
        DPT.[Name] [ProductType],
        SUM([FinishedGameCycleCount]) [NumOfGames],
        SUM([FinishedGameCycleCount]) *100. / SUM(SUM([FinishedGameCycleCount])) OVER() [percentage]
    FROM [WarehouseMgmt].[FactGameAgr] FWA
    JOIN [WarehouseMgmt].[DimPlayer] DPL ON FWA.[PlayerId] = DPL.[Id]
    JOIN [WarehouseMgmt].[DimGame] DG ON FWA.[GameId] = DG.[Id]
    JOIN [WarehouseMgmt].[DimProductType] DPT ON DPT.Id = FWA.ProductTypeId
    WHERE  [WarehouseMgmt].[GetDateTimeFromTimeId](TimeId) >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)
    GROUP BY DPT.[Name]