CODEWITHSUNDEEP
sqloracle-databasesyntax
user1255695
user1255695

Reputation: 23

Complex summing in SQL

I'm working with a relational database that uses SQL99.

I have a series of 10 columns, each of the 10 columns contain a number value.

I need to sum each column individually and then take those sums and add them all together to get an overall sum. Then I must divide the overall sum by 15.

I've tried every format I can think of and have yet to return any results. I have no idea what the syntax should look like.

Upvotes: 2

Views: 225

Answers (3)

Lynn Crumbling
Lynn Crumbling

Reputation: 13377

select 
      sum(col1) as sum1, 
      sum(col2) as sum2, 
      sum(col3) as sum3, 
      sum(col4) as sum4,
      sum(col5) as sum5, 
      sum(col6) as sum6, 
      sum(col7) as sum7, 
      sum(col8) as sum8,
      sum(col9) as sum9, 
      sum(col10) as as sum10,
      sum( col1 + col2 + col3 + col4 + col5 + col6 + col7 + col8 + col9 + col10) as overallsum,
      sum( col1 + col2 + col3 + col4 + col5 + col6 + col7 + col8 + col9 + col10) / 15 as dividedsum
   from 
      tablename

Upvotes: 3

David Faber
David Faber

Reputation: 12495

SELECT SUM(subsum) / 15 FROM (
   SELECT SUM(column1) AS subsum
      FROM table
     UNION ALL
    SELECT SUM(column2) AS subsum
      FROM table
     UNION ALL
    ...
    SELECT SUM(column10) AS subsum
      FROM table
)

Upvotes: 0

Sam DeHaan
Sam DeHaan

Reputation: 10325

SELECT SUM(col1), SUM(col2)..., SUM(col1 + col2 + col3 + col4...)/15
FROM TABLENAME
GROUP BY 1=1

Upvotes: 6

Related Questions