question

ravikumar-1532 avatar image
0 Votes"
ravikumar-1532 asked EchoLiu-msft edited

Select distinct values from one column and sum another column

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-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)? So that we’ll get a right direction and make some test.
In addition, in the screenshot of the Current result you provided, what are @fromDate Date and @toDate?

Echo

0 Votes 0 ·

Hi @EchoLiu-MSFT tq for your response @fromDate Date and @toDate are the textboxes in my winform which is used for filtering :
here is the table structure(data is toll confidential , but i think you'll get the idea by seeing the table structure):
the only required columns for my query are Department , date , order qty & rejection qty .

 SELECT TOP 1000 [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]
   FROM [PINQC].[dbo].[Semicon_NPD]


0 Votes 0 ·

Sorry, what I want to ask is actually the specific values of @fromDate Date and @toDate you got the current result. But if other data cannot be provided, the values of these two parameters do not need to be known.

Please refer to the answer I provided, it has not been tested due to lack of data.

Echo

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

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.



image.png (3.7 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EchoLiu-msft thank you so much for your help ...order qty and rejection qty is perfect , but as you said percentage value is coming different , for ex: 32/375 = 8.5% , but it is coming 5.57% , maybe i was wrong all along , could you please suggest me how to correct it ?

0 Votes 0 ·

According to your data, SUM(Rejection_Qty) does not filter duplicate order numbers, while SUM(Order_Qty) filters out duplicate order numbers. Is this your calculation rule?

0 Votes 0 ·

Since the comment cannot post the long code, please check the answer list for the latest solution.

0 Votes 0 ·
SQLZealots avatar image
1 Vote"
SQLZealots answered SQLZealots commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hi @SQLZealots thank you for your response , on execution i am getting this below error:


 Msg 8120, Level 16, State 1, Line 2
 Column 'Semicon_NPD.Order_Number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


0 Votes 0 ·

the original answer is updated.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered ravikumar-1532 edited

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hi @EchoLiu-msft again same error:

 Msg 8120, Level 16, State 1, Procedure orderqtyper, Line 7
 Column 'cte.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0 Votes 0 ·

I have updated the answer please check if it works.

0 Votes 0 ·

Hi @EchoLiu-msft as the sql insert statement was long i have updated in question pls look into it

0 Votes 0 ·
EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered EchoLiu-msft edited
 ;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



image.png (3.6 KiB)
image.png (4.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you so much ...you are my life saver

0 Votes 0 ·