SQL Server Return Average based on Per Item in each line

5hcedyr0 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(121)

I have Data as follows from a select Statement from ItemDetails Table:

INPUT [Same Ref]:
| ItemNo | ItemValue | Ref |
| ------------ | ------------ | ------------ |
| ITM001 | 81 | A1 |
| ITM002 | 45 | B1 |
| ITM001 | 81 | A1 |

Expected OUTPUT [Same Ref - Do AVG]:

ItemNo	ItemValue	Ref
ITM001	40.50	A1
ITM002	45	B1
ITM001	40.50	A1

INPUT [Same Ref but Different ItemValue] :

ItemNo	ItemValue	Ref
ITM001	81	A1
ITM002	45	B1
ITM001	30	A1

Expected OUTPUT [No Change to Value]:

ItemNo	ItemValue	Ref
ITM001	81	A1
ITM002	45	B1
ITM001	30	A1

Expected INPUT [ Different Ref Same ItemValue] :

ItemNo	ItemValue	Ref
ITM001	81	A1
ITM002	45	B1
ITM001	81	A2

Expected OUTPUT [ No Changes to ItemValue]:

ItemNo	ItemValue	Ref
ITM001	81	A1
ITM002	45	B1
ITM001	81	A2

Rows are duplicating due to other column s.

SELECT ItemNo, AVG(ItemValue) OVER (PARTITION BY ItemNo) / COUNT(*) OVER(PARTITION BY ItemNo) AS ItemValue 
FROM ItemDetails ;

seems not working .

Can anyone please help ?

sql-server

来源：https://stackoverflow.com/questions/77105045/return-average-based-on-per-item-in-each-line

1条答案

按热度按时间

b1zrtrql1#

This gives the outputs you indicated in your post; note that if your ItemValue column is a float, this will behave unpredictably and should be cast to a decimal or numeric type first.

/*
CREATE TABLE #ItemDetails (
  ItemNo NVARCHAR(255),
  ItemValue DECIMAL(10,2)
)
INSERT INTO #ItemDetails VALUES ('ITM001', 81)
INSERT INTO #ItemDetails VALUES ('ITM002', 45)
INSERT INTO #ItemDetails VALUES ('ITM001', 81)
INSERT INTO #ItemDetails VALUES ('ITM003', 81)
INSERT INTO #ItemDetails VALUES ('ITM004', 45)
INSERT INTO #ItemDetails VALUES ('ITM003', 30)
*/
WITH DuplicateCounts AS (
  SELECT
    ItemNo,
    ItemValue,
    COUNT(*) AS DuplicateCount
  FROM
    #ItemDetails
  GROUP BY
    ItemNo,
    ItemValue
)
SELECT 
  I.ItemNo,
  I.ItemValue/D.DuplicateCount AS ItemValue
FROM 
  #ItemDetails I
INNER JOIN
  DuplicateCounts D
ON
  I.ItemNo = D.ItemNo
  AND I.ItemValue = D.ItemValue

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Return Average based on Per Item in each line

1条答案

相关问题

热门标签

最新问答