union query 1

[captlid]

is it possible to sum the results of a union all query?

example:

SELECT col1 FROM table WHERE col3 = 'value 1'
UNION ALL
SELECT col2 FROM table WHERE col4 = 'value 1'

can all the results from col1 and col2 be summed together?

thanks,

 

[TonyGroves]

You could use a subquery (assuming MySQL 4.1):
[tt]
SELECT SUM(col1)
FROM
(
SELECT col1 FROM table WHERE col3 = 'value 1'
UNION ALL
SELECT col2 FROM table WHERE col4 = 'value 1'
)
u
[/tt]
or, a bit more exotic, and possibly slower, but works on earlier versions:
[tt]
SELECT SUM(col1*(col3='value 1')+col2*(col4='value 1'))
FROM table
[/tt]

 

[captlid]

Thank you so much especially for showing the query for versions before 4.1.

What does the asterik symbol denote?

 

[TonyGroves]

Multiplication.

(col3='value 1') evaluates to either 1 or 0, true or false.
So, col1*(col3='value 1') evaluates either to the value of col1, or to zero.