SQL SUM question

Hi I have a question about SUM in sql,

I have开发者_StackOverflow a query that looks like this

SELECT 
 SUM ( table_one.field + table_two.field )  as total_field
 SUM ( total_field + table_one.anotherfield )
FROM 
 table_one
JOIN
 table_two ON table_one.id = table_two.id
WHERE 
 table_one = 1

But this doesn't work ( dont mind possible typing errors in JOIN statement, only the second SUM is the probly the query works perfecly without that SUM)

Is there another way to do it, as I need the total_field within my application. I can ofcource add those numbers within the application but I prefer to do it in sql.

You cannot use the column alias in an aggregate to reference the value, just SUM again;

SELECT 
 SUM ( table_one.field + table_two.field ) as total_field, --your missing a , also
 SUM ( table_one.field + table_two.field + table_one.anotherfield )
FROM 
 table_one
JOIN
 table_two ON table_one.id = table_two.id
WHERE 
 table_one = 1

SUM is an aggregate function. This means you can aggregate data from a field over several tuples and sum it up into a single tuple.

What you want to do is this:

SELECT 
  table_one.field + table_two.field,
  table_one.field + table_two.field + table_one.anotherfield

or maybe this:

SELECT 
  SUM(table_one.field) + SUM(table_two.field),
  SUM(table_one.field) + SUM(table_two.field) + SUM(table_one.anotherfield)

Try replacing "total_field" with "table_one.field + table_two.field" in second SUM().

The name "total_field" is an alias and as such cannot be used in an aggregate functions The easiest and quickest way is to simply replace the code for total_field in the second calculation.

SELECT
   SUM ( ISNULL(table_one.field,0) + ISNULL(table_two.field,0) )  as total_field
   SUM ( ISNULL(table_one.field,0) + ISNULL(table_two.field,0) +     IsNUll(table_one.anotherfield,0) )
from
  table_one

As your code doesn't cater for a null value in the fields you may get warnings when sum the values. I would suggest using IsNull as above and if there is a null value just treat it as 0.

You could use a subquery like this:

SELECT
  total_field,
  total_field + sum_anotherfield
FROM (
  SELECT 
    SUM(table_one.field + table_two.field) AS total_field,
    SUM(table_one.anotherfield) AS sum_anotherfield
  FROM 
    table_one
  JOIN
    table_two ON table_one.id = table_two.id
  WHERE 
    table_one.somefield = 1
) x