Select distinct values from one column and sum another column

ravi kumar 331 Reputation points
2021-04-06T07:15:24.157+00:00

I have this stored procedure shown here which calculates the order qty, rejection and percentage for rejection against each department, but I have an issue: for summing the order qty, I need to consider only distinct order number to sum the order qty, but for rejection qty all rows should be considered. How to achieve this?

Stored procedure: 

CREATE PROCEDURE orderqtyper  
    @fromDate Date,  
    @toDate Date  
AS  
    SELECT   
        Department,   
        SUM(Order_Qty) AS [Order Qty],  
        SUM(Rejection_Qty) AS [Rejection Qty],  
        FORMAT((SUM(Rejection_Qty) * 100.0 / NULLIF(SUM(Order_Qty), 0) / 100), 'P') AS Percentage     
    FROM   
        Semicon_NPD  
    WHERE   
        (Date BETWEEN @fromDate AND @toDate)  
    GROUP BY   
        Department  
    ORDER BY   
        Percentage DESC

Sample table:
XgHcm.png

Current result:
EarqO.png

Expected result (if the the order number is the same, it should take sum of only unique values for order qty but sum of all for rejections qty):
qt7Oz.png

sql insert statement: 

CREATE TABLE mytable(  
   ID                INTEGER  NOT NULL PRIMARY KEY   
  ,Date              VARCHAR(10) NOT NULL  
  ,MRP               VARCHAR(7) NOT NULL  
  ,Material_Number   INTEGER   
  ,Material_Type     VARCHAR(5)  
  ,Weight_in_Kg      NUMERIC(5,3)  
  ,Order_Number      INTEGER   
  ,Order_Qty         INTEGER   
  ,Rejection_Qty     INTEGER   
  ,Department        VARCHAR(12)  
  ,Reason_Brief      VARCHAR(17)  
  ,Reason_Explained  VARCHAR(57)  
  ,Responsibility_1  VARCHAR(8)  
  ,Responsibility_2  VARCHAR(1)  
  ,Responsibility_3  VARCHAR(6)  
  ,Responsibility_4  VARCHAR(6)  
  ,Corrective_Action VARCHAR(58)  
  ,Action_Taken      VARCHAR(10)  
  ,Action_Status     VARCHAR(12)  
);  
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (315,'4/5/2021','901',ABC,'AB',0.035,3847478,278,1,'EDM','AB','AB','AB','AB','AB','AB','AB','AB','AB');  
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (314,'4/5/2021','9',DEF,'bd',0.045,3942733,17,1,'Milling','AB','AB','AB','AB','AB','AB','AB','AB','AB');  
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (317,'4/5/2021','9',GHI,'AB',0.302,3942733,17,2,'Milling','AB','AB','AB','AB','AB','AB','AB','AB','AB');  
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (319,'4/5/2021','9',JKL,'bd',0.154,3957917,60,1,'Milling','AB','AB','AB','AB','AB','AB','AB','AB','AB');  
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (320,'4/5/2021','9',MNO,'AB',0.415,3962344,10,10,'Pre-Material','AB','AB','AB','AB','AB','AB','AB','AB','AB');  
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (316,'4/5/2021','9',PQR,'bd',0.016,3930483,427,2,'Turning','AB','AB','AB','AB','AB','AB','AB','AB','AB');  
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (318,'4/5/2021','9',TST,'AB',0.026,3953441,197,2,'Turning','AB','AB','AB','AB','AB','AB','AB','AB','AB');  
INSERT INTO mytable(ID,Date,MRP,Material_Number,Material_Type,Weight_in_Kg,Order_Number,Order_Qty,Rejection_Qty,Department,Reason_Brief,Reason_Explained,Responsibility_1,Responsibility_2,Responsibility_3,Responsibility_4,Corrective_Action,Action_Taken,Action_Status) VALUES (318,'4/5/2021','9',UVW,'bd',0.026,3953441,197,5,'Turning','AB','AB','AB','AB','AB','AB','AB','AB','AB');
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,607 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-04-07T07:30:35.867+00:00

    Please try: 

        CREATE TABLE mytable(ID INT  NOT NULL PRIMARY KEY ,[Date] Date  
                        ,Order_Number INT ,Order_Qty INT ,Rejection_Qty INT   
     ,Department  VARCHAR(12))  
    INSERT INTO mytable VALUES(1,'01-03-2021',123456,100,10,'ABC')  
                              ,(2,'02-03-2021',123456,100,15,'ABC')  
       ,(3,'03-03-2021',543218,200,8,'DEF')  
       ,(4,'04-03-2021',543218,200,13,'DEF')  
       ,(5,'05-03-2021',124589,150,12,'ABC')  
       ,(6,'06-03-2021',157489,175,11,'DEF')  
       ,(7,'07-03-2021',325676,200,21,'ABC')  
      
    SELECT * FROM mytable  
      
    ;WITH cte  
      AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY Order_Number ORDER BY Order_Number)rr FROM mytable)  
     ,cte2 AS(SELECT Department, SUM(Order_Qty) AS [Order Qty]   
             FROM (SELECT * FROM cte WHERE rr<2) t  
             WHERE (Date BETWEEN  '01-03-2021' AND '07-03-2021')  
             GROUP BY  Department)  
     ,cte3 AS(SELECT Department,SUM(Rejection_Qty) AS [Rejection Qty],  
                     FORMAT((SUM(Rejection_Qty) * 100.0 / NULLIF(SUM(Order_Qty), 0) / 100), 'P') AS [Percentage]     
             FROM mytable  
             WHERE (Date BETWEEN  '01-03-2021' AND '07-03-2021')  
             GROUP BY Department)  
          
     SELECT c2.*,c3.[Rejection Qty],c3.[Percentage] FROM cte2 c2  
     JOIN cte3 c3 ON c2.Department=c3.Department  
      
      
    DROP TABLE mytable

    Output：
    85098-image.png

    The value of Percentage seems to be different from your expected output, but I calculated it according to your formula.

    Regards
    Echo

    If the answer is helpful, please click "Accept Answer" and upvote it.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. SQLZealots 276 Reputation points
    2021-04-06T07:40:47.32+00:00

    Try something like below, could not test it. 

        ;with cteOrderQty as (  
    Select Distinct Department, Ordernumber, OrderQty From Semicon_NPD  
         WHERE   
             (Date BETWEEN @fromDate AND @toDate)  
         )  
      ,cteGrpOrdResult as (Select Department, sum(OrderQty) OrdQty From cteorderQty GROUP BY   
             Department)  
      
      ,cteRejQty as (  
    Select  Department, Ordernumber, Rejection_Qty From Semicon_NPD  
         WHERE   
             (Date BETWEEN @fromDate AND @toDate)  
        ),  
    ,cteGrpRejResult as (Select Department, sum(Rejection_Qty) Rejection_Qty From cteRejQty  GROUP BY   
             Department)  
    Select A.Department,A.OrdQty,B.Rejection_qty,  
    FORMAT((SUM(Rejection_Qty) * 100.0 / NULLIF(SUM(OrdQty), 0) / 100), 'P') AS Percentage    
    From cteGrpOrdResult A  
    inner join cteGrpRejResult B on a.department=b.department
    1 person found this answer helpful.
  2. EchoLiu-MSFT 14,571 Reputation points
    2021-04-07T09:08:30.223+00:00 
    ;WITH cte  
  AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY Order_Number ORDER BY Order_Number)rr FROM mytable)  
 ,cte2 AS(SELECT Department, SUM(Order_Qty) AS [Order_Qty]  
         FROM (SELECT * FROM cte WHERE rr<2) t  
         WHERE (Date BETWEEN  '01-03-2021' AND '07-03-2021')  
         GROUP BY  Department)  
 ,cte3 AS(SELECT Department,SUM(Rejection_Qty) AS [Rejection Qty]  
         FROM mytable  
         WHERE (Date BETWEEN  '01-03-2021' AND '07-03-2021')  
         GROUP BY Department)  
      
 SELECT c2.*,c3.[Rejection Qty],FORMAT((c3.[Rejection Qty]*100.0/NULLIF(Order_Qty, 0) / 100), 'P') AS [Percentage]  
 FROM cte2 c2  
 JOIN cte3 c3 ON c2.Department=c3.Department

    85271-image.png

    1 person found this answer helpful.
  3. EchoLiu-MSFT 14,571 Reputation points
    2021-04-06T08:29:29.95+00:00

    Please try: 

     CREATE PROCEDURE orderqtyper
     @fromDate Date,
     @toDate Date
 AS
 WITH cte
 AS(SELECT DISTINCTE * FROM Semicon_NPD)
,cte2 AS(SELECT Department, SUM(Order_Qty) AS [Order Qty] FROM cte
  WHERE (Date BETWEEN @fromDate AND @toDate)
         GROUP BY  Department)
,cte3 AS(SELECT Department,FORMAT((SUM(Rejection_Qty) * 100.0 / NULLIF(SUM(Order_Qty), 0) / 100), 'P') AS Percentage   
        FROM Semicon_NPD
        WHERE (Date BETWEEN @fromDate AND @toDate)
        GROUP BY Department)

SELECT * FROM cte2 c2
JOIN cte3 c3 ON c2.Department=c3.Department 
ORDER BY Percentage DESC

    Echo