how do i get the sum of the 4th column

Question

this statement will generate a 4 column table:

SELECT shipped.badguy AS badguy, shipped.sdate AS LineDate,
        'Delivery' AS Legend, -price*quantity AS amount
    FROM product JOIN shipped ON (product.id = shipped.product)
UNION
  SELECT receipt.badguy, receipt.rdate,notes, amount
  FROM receipt

how do i get the total sum of the 4th of column of what the above generates?

Answer 1

SUM(-price*quantity)

huh?

Answer 2

Try

sum(price*quantity)

or combine both query as

SELECT badguy,rdate,notes,SUM(AMOUNT) FROM(
        SELECT shipped.badguy AS badguy, shipped.sdate AS LineDate, 
        'Delivery' AS Legend, -price*quantity AS amount 
    FROM product JOIN shipped ON (product.id = shipped.product) 
UNION 
  SELECT receipt.badguy, receipt.rdate,notes, amount 
  FROM) A

Answer 3

I like to use subqueries for everything.

SELECT SUM(results.amount)
FROM
(
SELECT -price*quantity AS amount 
    FROM product JOIN shipped ON (product.id = shipped.product) 
UNION 
  SELECT amount 
  FROM  
...
) results

Answer 4

Wrap your query in another query, where the outer query just gets the sum you want. This makes your query a subquery.

SELECT SUM(amount) FROM (
   SELECT shipped.badguy AS badguy, shipped.sdate AS LineDate,
           'Delivery' AS Legend, -price*quantity AS amount
       FROM product JOIN shipped ON (product.id = shipped.product)
   UNION
     SELECT receipt.badguy, receipt.rdate,notes, amount
     FROM <...>
)