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,

There is nowhere enough information to understand what you are asking, let alone offer any help. Try [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) as a starting point. — Sean Lange, Oct 09 '18 at 18:33
You'll want to read about "aggregate functions" in the transact-sql/sql-server docs — giorgiga, Oct 09 '18 at 18:35
sample, expected results, what you have tried so far, give more details ? — iSR5, Oct 09 '18 at 18:43

score 0 · Answer 1 · answered Oct 09 '18 at 18:54

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

score 0 · Answer 2 · answered Oct 09 '18 at 19:07

Perhaps you need this ?

DECLARE 
    @t  TABLE (
        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 @t 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)


SELECT
    (col1 + col2 + col3 + col4 + col5 + col6 + col7 + col8 + col9 + col10) / 10 Median
FROM (
SELECT 
    SUM(col1) col1
,   SUM(col2) col2
,   SUM(col3) col3
,   SUM(col4) col4
,   SUM(col5) col5
,   SUM(col6) col6
,   SUM(col7) col7
,   SUM(col8) col8
,   SUM(col9) col9
,   SUM(col10) col10
FROM @t 
) D

Jim Todd · Answer 3 · 2018-10-09T19:32:08.523

This query will also do:

        Select sum(sumOfColumns)/2 as medianOfColumns from (select * from (
        (
        Select ROW_NUMBER() over (ORDER BY sumOfColumns ASC) as rnum, sumOfColumns from
     (
        Select sum(col1) as sumOfColumns from temp_table group by col1
        UNION ALL
        Select sum(col2) as sumOfColumns from temp_table group by col2
        UNION ALL
        Select sum(col3) as sumOfColumns from temp_table group by col3
        UNION ALL
        Select sum(col4) as sumOfColumns from temp_table group by col4
        UNION ALL
        Select sum(col5) as sumOfColumns from temp_table group by col5
        UNION ALL
        Select sum(col6) as sumOfColumns from temp_table group by col6
        UNION ALL
        Select sum(col7) as sumOfColumns from temp_table group by col7
        UNION ALL
        Select sum(col8) as sumOfColumns from temp_table group by col8
        UNION ALL
        Select sum(col9) as sumOfColumns from temp_table group by col9
        UNION ALL
        Select sum(col10) as sumOfColumns from temp_table group by col10
        ) 
        ) 
)where rnum BETWEEN 5 and 6
);

SQL - operation on the Column sum of a Table

3 Answers3

Linked