SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE oc_vdata
  (
     data1  DECIMAL(18, 2),
     partno VARCHAR(20)
  )

INSERT INTO oc_vdata
VALUES      (100,
             '1234'),
            (250,
             '1234'),
            (300,
             '1234'),
            (150,
             '1234'),
            (200,
             '2345'),
            (450,
             '2345'),
            (2300,
             '2345'),
            (2150,
             '2345');

CREATE TABLE item_codes
  (
     spec_no   VARCHAR(20),
     item_code VARCHAR(100)
  );

INSERT INTO item_codes
VALUES      ('1234',
             'ITEM1'),
            ('2345',
             'ITEM2') 
10 rows affected

SELECT DISTINCT ITEM_CODES.ITEM_CODE, 
                    AVG(OC_VDATA.DATA1) OVER(PARTITION BY ITEM_CODES.ITEM_CODE) AS Mean, 
                    STDEVP(OC_VDATA.DATA1) OVER(PARTITION BY ITEM_CODES.ITEM_CODE) AS StandardDev,
                    PERCENTILE_CONT(0.5) 
                        WITHIN GROUP (ORDER BY OC_VDATA.DATA1) 
                        OVER (PARTITION BY ITEM_CODES.ITEM_CODE) AS Median
    FROM            OC_VDATA 
                    INNER JOIN
                    ITEM_CODES 
                    ON LEFT(OC_VDATA.PARTNO, 12) = ITEM_CODES.SPEC_NO 
   


ITEM_CODE
Mean
StandardDev
Median


ITEM1
200.000000
79.0569415042095
200

ITEM2
1275.000000
955.57574268082
1300


SELECT        ITEM_CODES.ITEM_CODE, 
                    AVG(OC_VDATA.DATA1) AS Mean, 
                    STDEVP(OC_VDATA.DATA1)  AS StandardDev,
                    PERCENTILE_CONT(0.5) 
                        WITHIN GROUP (ORDER BY OC_VDATA.DATA1) 
                        OVER (PARTITION BY ITEM_CODES.ITEM_CODE) AS Median
    FROM            OC_VDATA 
                    INNER JOIN
                    ITEM_CODES 
                    ON LEFT(OC_VDATA.PARTNO, 12) = ITEM_CODES.SPEC_NO 
   GROUP BY ITEM_CODES.ITEM_CODE
Msg 8120 Level 16 State 1 Line 5
Column 'OC_VDATA.data1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
ITEM_CODE	Mean	StandardDev	Median
ITEM1	200.000000	79.0569415042095	200
ITEM2	1275.000000	955.57574268082	1300