CODEWITHSUNDEEP
sqlsql-server
Tommy Sayugo
Tommy Sayugo

Reputation: 375

How to calculate percent from Group By Select

I Have a production table with this column:

PRODUCTION_DATE  EMPLOYEE  ITEM  ITEM_QTY
-----------------------------------------
1/1/2000           ANA      A       5
1/1/2000           ANA      B       8
1/1/2000          CAROL     A       3
1/1/2000          CAROL     B       9
1/2/2000           ANA      A       4
1/2/2000           ANA      B       8
1/2/2000          CAROL     A       5
1/2/2000          CAROL     B       7

And then I to group this data weekly with this code:

SELECT      DATEPART(WK,[PRODUCTION_DATE]) AS [WEEK_PROD],
            [EMPLOYEE],
            [ITEM],
            SUM([ITEM_QTY]) AS [TOTAL_QTY],
FROM        PRODUCTION_TABLE

GROUP BY    YEAR([PRODUCTION_DATE]),
            DATEPART(WK,[PRODUCTION_DATE]),
            [EMPLOYEE],
            [ITEM]

With that code above, I can show data like this:

WEEK_PROD  EMPLOYEE  ITEM  TOTAL_QTY
-------------------------------------
    1        ANA       A       9
    1        ANA       B       16
    1       CAROL      A       8
    1       CAROL      B       16

The problem is, I want to show percent for each item by employee like this:

WEEK_PROD  EMPLOYEE  ITEM  TOTAL_QTY  PERCENTAGE
------------------------------------------------
    1        ANA       A       9          36
    1        ANA       B       16         64
    1       CAROL      A       8         33.33
    1       CAROL      B       16        66.67

How can I do that?

Thanks in advance

Upvotes: 0

Views: 102

Answers (4)

dragonfly02
dragonfly02

Reputation: 3669

Window functions can help here and in my opinion it's more natural than query expression. 

SELECT      DISTINCT DATEPART(WK,[PRODUCTION_DATE]) AS [WEEK_PROD],
            [EMPLOYEE],
            [ITEM],
            SUM([ITEM_QTY]) OVER(PARTITION BY EMPLOYEE, ITEM) AS [TOTAL_QTY],
            SUM([ITEM_QTY]) OVER(PARTITION BY EMPLOYEE, ITEM) * 1.0 / SUM([ITEM_QTY]) OVER(PARTITION BY EMPLOYEE) AS [PERCENTAGE]
FROM        dbo.PRODUCTION_TABLE

Upvotes: 1

etsa
etsa

Reputation: 5060

This is a query which only add a super query to your (unchanged, in the inner part):

SELECT A.*
, (TOTAL_QTY+0.00)/SUM(TOTAL_QTY) OVER (PARTITION BY EMPLOYEE) *100  AS PERC 
FROM 
    (SELECT      DATEPART(WK,[PRODUCTION_DATE]) AS [WEEK_PROD],
                [EMPLOYEE],
                [ITEM],
                SUM([ITEM_QTY]) AS [TOTAL_QTY]          
    FROM        mytable
    GROUP BY    YEAR([PRODUCTION_DATE]),
                DATEPART(WK,[PRODUCTION_DATE]),
                [EMPLOYEE],
                [ITEM]
            ) A

Output:

WEEK_PROD   EMPLOYEE ITEM TOTAL_QTY   PERC
----------- -------- ---- ----------- ---------------------------------------
1           ANA      A    9           36.0000000000000
1           ANA      B    16          64.0000000000000
1           CAROL    A    8           33.3333333333300
1           CAROL    B    16          66.6666666666600

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

Use sub query in JOIN statement :

SELECT [EMPLOYEE],[ITEM],SUM([ITEM_QTY]) AS [TOTAL_QTY],
       (SUM([ITEM_QTY]) * 100) / Total
FROM #Table
JOIN 
(
   SELECT [EMPLOYEE] _EMPLOYEE ,SUM([ITEM_QTY]) Total
   FROM #Table
   GROUP BY [EMPLOYEE]
) A ON EMPLOYEE = _EMPLOYEE
GROUP BY [EMPLOYEE],[ITEM],Total

Upvotes: 0

SHD
SHD

Reputation: 409

Try this query,

;WITH [CTE_EMP]
AS
(
SELECT      DATEPART(WK,[PRODUCTION_DATE]) AS [WEEK_PROD],
            [EMPLOYEE],
            [ITEM],
            SUM([ITEM_QTY]) AS [TOTAL_QTY]
FROM        @PRODUCTION_TABLE
GROUP BY    YEAR([PRODUCTION_DATE]),
            DATEPART(WK,[PRODUCTION_DATE]),
            [EMPLOYEE],
            [ITEM]
)

SELECT *, ([TOTAL_QTY] * 100) / SUM([TOTAL_QTY]) OVER (PARTITION BY [EMPLOYEE],[WEEK_PROD]) AS [PERCENTAGE]
FROM [CTE_EMP]

Upvotes: 0

Related Questions