# Select distinct values from one column and sum another column

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:

Current result:

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):

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.
13,462 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,613 questions

1. 14,581 Reputation points
2021-04-07T07:30:35.867+00:00

``````    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：

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

Regards
Echo

1. 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

``````

2. 14,581 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
``````

3. 14,581 Reputation points
2021-04-06T08:29:29.95+00:00

`````` 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