--WITH OrdersRN AS
--(
-- SELECT EmployeeID, Value,
-- ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Value) AS RowNum,
-- COUNT(*) OVER(PARTITION BY EmployeeID) AS Cnt
-- FROM dbo.VOrders
--)
--SELECT EmployeeID, Value, RowNum, Cnt
--FROM OrdersRN
--WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2);
declare @mtables table (id INT, name VARCHAR(10))
insert @mtables (id, name) values(1, 'gv'),(2, 'gv'),(3, 'gv'),(4, 'gv')/*,(5, 'gv')*/,(1, 'cc'),(2, 'cc'),(3, 'cc'),(4, 'cc'),(5, 'cc')
-- display the data in the table
select *, ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) AS RowNum from @mtables order by name, id
;WITH OrdersRN AS
(
SELECT ID, name,
ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) AS RowNum,
COUNT(*) OVER(PARTITION BY name) AS Cnt
FROM @mtables
)
SELECT name, AVG(id * 1.0) as Median
FROM OrdersRN
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)
group by name