sql server - SQL - operation on the Column sum of a Table

Question

Let say I have a Table in SQL server with 10 columns with header Col-1, Col-2, ... Col-10. Now I want to write an SQL query which will first calculate the Column-sums for all 10 columns individually - thereby a row of length 10 will be generated, and then calculate the median of the values of that generated row.

To add further detail, let say I have below table. What I need SQL is to calculate the Median of Col-sum values as in below illustration.

    Col - 1 Col - 2 Col - 3 Col - 4 Col - 5 Col - 6 Col - 7 Col - 8 Col - 9 Col - 10
    0.4763  0.9746  0.5082  0.8707  0.3608  0.6984  0.9326  0.9983  0.1441  0.6882
    0.9396  0.9358  0.6548  0.8046  0.3274  0.3072  0.1275  0.8273  0.9785  0.9618
    0.6656  0.7000  0.1664  0.0341  0.9804  0.4973  0.2023  0.4619  0.9759  0.0456
    0.9707  0.3495  0.8282  0.6389  0.8845  0.8833  0.8424  0.6087  0.4165  0.6986
    0.5992  0.8121  0.0324  0.9134  0.9613  0.6163  0.1110  0.8911  0.7429  0.4397
    0.1069  0.6702  0.6478  0.9055  0.3594  0.0060  0.0556  0.4216  0.0578  0.2796
Col-sum 3.7583  4.4421  2.8378  4.1672  3.8737  3.0085  2.2714  4.2088  3.3157  3.1135
**Median    3.537024951**                                   

Any pointer how to implement such in SQL will be highly appreciated. Thanks,

Answer 1

Let's start with the sample data in a temp table:

CREATE TABLE #temp
(
    Col1 DECIMAL(18, 4)
    ,Col2 DECIMAL(18, 4)
    ,Col3 DECIMAL(18, 4)
    ,Col4 DECIMAL(18, 4)
    ,Col5 DECIMAL(18, 4)
    ,Col6 DECIMAL(18, 4)
    ,Col7 DECIMAL(18, 4)
    ,Col8 DECIMAL(18, 4)
    ,Col9 DECIMAL(18, 4)
    ,Col10 DECIMAL(18, 4)
)

INSERT INTO #temp
(
    Col1
    ,Col2
    ,Col3
    ,Col4
    ,Col5
    ,Col6
    ,Col7
    ,Col8
    ,Col9
    ,Col10
)
VALUES
(0.4763, 0.9746, 0.5082, 0.8707, 0.3608, 0.6984, 0.9326, 0.9983, 0.1441, 0.6882)
,(0.9396, 0.9358, 0.6548, 0.8046, 0.3274, 0.3072, 0.1275, 0.8273, 0.9785, 0.9618)
,(0.6656, 0.7000, 0.1664, 0.0341, 0.9804, 0.4973, 0.2023, 0.4619, 0.9759, 0.0456)
,(0.9707, 0.3495, 0.8282, 0.6389, 0.8845, 0.8833, 0.8424, 0.6087, 0.4165, 0.6986)
,(0.5992, 0.8121, 0.0324, 0.9134, 0.9613, 0.6163, 0.1110, 0.8911, 0.7429, 0.4397)
,(0.1069, 0.6702, 0.6478, 0.9055, 0.3594, 0.0060, 0.0556, 0.4216, 0.0578, 0.2796)

Next, we're going to use a couple of CTEs to get the data into the form we need to calculate the Median. BTW, I'm using the median algorithm posted in the answer here: Function to Calculate Median in SQL Server

The first CTE gets the SUMs of all columns. The second CTE "pivots" the columns into rows. The main query then finds the median.

;WITH Sums AS
(
    SELECT Col1 = SUM(col1)
          ,Col2 = SUM(col2)
          ,Col3 = SUM(col3)
          ,Col4 = SUM(col4)
          ,Col5 = SUM(col5)
          ,Col6 = SUM(col6)
          ,Col7 = SUM(col7)
          ,Col8 = SUM(col8)
          ,Col9 = SUM(col9)
          ,Col10  = SUM(col10)
    FROM #temp
)
,SumVals AS
(
    SELECT SumVal = Col1 
    FROM Sums
    UNION ALL
    SELECT SumVal = Col2 
    FROM Sums
    UNION ALL
    SELECT SumVal = Col3 
    FROM Sums
    UNION ALL
    SELECT SumVal = Col4 
    FROM Sums
    UNION ALL
    SELECT SumVal = Col5 
    FROM Sums
    UNION ALL
    SELECT SumVal = Col6 
    FROM Sums
    UNION ALL
    SELECT SumVal = Col7 
    FROM Sums
    UNION ALL
    SELECT SumVal = Col8 
    FROM Sums
    UNION ALL
    SELECT SumVal = Col9 
    FROM Sums
    UNION ALL
    SELECT SumVal = Col10 
    FROM Sums
)
SELECT
   Median = AVG(SumVal)
FROM
(
   SELECT
      SumVal,
      ROW_NUMBER() OVER (ORDER BY SumVal ASC) AS RowAsc,
      ROW_NUMBER() OVER (ORDER BY SumVal DESC) AS RowDesc
   FROM SumVals sv
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)


DROP TABLE #temp

Result:

Median
3.537000