CODEWITHSUNDEEP
sqlt-sql
Justin Greenwood
Justin Greenwood

Reputation: 91

Working Out a Percentage of a Count Total

Morning All

Ok so I know how to get the percentage using like.

ROUND(CAST(SUM(Col1)AS FLOAT)  *100.0 / COUNT(*),2)

But here is where I am Stuck.

DECLARE @pYWK INT
SET @pYWK = (SELECT YWK FROM CHDS_Management.dbo.Calendar 
          WHERE DT = (SELECT DATEADD(WEEK,-3, CONVERT(DATE,GETDATE()))));

SELECT
    ORD.ProductWhsLocation AS 'Location', 
    COUNT(ORD.ProductWhsLocation) AS 'Picked'
FROM CHDS_Common.dbo.OMOrder AS ORD
    INNER JOIN CHDS_Management.dbo.PickZoneControl AS PZC ON 
LEFT(ORD.ProductWhsLocation,3) = PZC.PickZone
    INNER JOIN CHDS_Management.dbo.Calendar AS CAL ON CAL.DT = 
ORD.EarliestPickDate
WHERE CAL.YWK = @pYWK AND ORD.PickedQty <> 0
GROUP BY ORD.ProductWhsLocation

What I am trying to work out is the % of what was picked from each loaction from the total picked.

Its Monday Morning here so I am sorry if its somthing stupid I am missing.

Thank you for any help on this one.

Upvotes: 2

Views: 47

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

Use Count(*)Over() to get the total number of location, use it to divide the Picked to get the percentage of picked in each location 

SELECT ORD.ProductWhsLocation        AS 'Location',
       Count(ORD.ProductWhsLocation) AS 'Picked',
       ( ( Count(ORD.ProductWhsLocation) * 1.0 ) / Sum(Count(ORD.ProductWhsLocation)) OVER() ) * 100.0
FROM   CHDS_Common.dbo.OMOrder AS ORD
       INNER JOIN CHDS_Management.dbo.PickZoneControl AS PZC
               ON LEFT(ORD.ProductWhsLocation, 3) = PZC.PickZone
       INNER JOIN CHDS_Management.dbo.Calendar AS CAL
               ON CAL.DT = ORD.EarliestPickDate
WHERE  CAL.YWK = @pYWK
       AND ORD.PickedQty <> 0
GROUP  BY ORD.ProductWhsLocation

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

This is a tricky question, because to obtain the percentages of each group (requiring one aggregation) we need to normalize those counts with the total count of the entire query (another aggregation). One approach is to place the base join query into a CTE. Then, query that CTE and also get the total count via a subquery on the same CTE.

WITH cte AS (
    SELECT
        ORD.ProductWhsLocation AS 'Location'
    FROM CHDS_Common.dbo.OMOrder AS ORD
    INNER JOIN CHDS_Management.dbo.PickZoneControl AS PZC
        ON LEFT(ORD.ProductWhsLocation,3) = PZC.PickZone
    INNER JOIN CHDS_Management.dbo.Calendar AS CAL
        ON CAL.DT = ORD.EarliestPickDate
    WHERE CAL.YWK = @pYWK AND ORD.PickedQty <> 0
)

SELECT
    Location,
    COUNT(Location),
    ROUND(100.0 * COUNT(Location) / (SELECT COUNT(*) FROM cte), 2) AS pct
FROM cte
GROUP BY
    Location;

Upvotes: 1

Related Questions