SQL query help

[_dobbo_]

Now you will have to bear with me as I'm not very experienced with SQL at all...

Running MS SQL 6.5

I want to return the sum of two query sets as a single value - it seems using "UNION" doesn't work - here's what i've got

select sum(productval)
from producttable
where blah = blah
union
select sum(productval)
from producttable
where blah = blah2

using this seems to only return the second sum value, rather than a union of the two - can anyone help?

[Liszt]

do you mean:

select sum(productval)
from producttable
where blah = blah
or blah = blah2

[_dobbo_]

Liszt said:

do you mean: select sum(productval) from producttable where blah = blah or blah = blah2

Sorry if I wasn't very clear, trying to keep it simple but in doing so I've confused the issue.

This is a simplified query but is the general gist of it:

select sum(productval)
from producttable
where productnumber = 17
union
select sum(productval)
from producttable
where productnumber != 17 and dateexpires > getdate()

Using the "or" expression you've suggested just seems to sit there and not return a result.

[pdV6]

_dobbo_ said:

select sum(productval) from producttable where productnumber = 17 union select sum(productval) from producttable where productnumber != 17 and dateexpires > getdate() Using the "or" expression you've suggested just seems to sit there and not return a result.

How's about:

select sum (productval) from (select productval from producttable where (productnumber=17) or (productnumber!=17 and dateexpires>getdate()))

or the more succint version that Liszt suggested:

select sum (productval) from producttable where (productnumber=17) or (productnumber!=17 and dateexpires>getdate())


>> Edited by pdV6 on Friday 7th October 15:02

[jimothy]

What do you mean by get the results as 1 value? The SQL you describe implies you want a results set containing 2 values, one from each sum...

[pdV6]

jimothy said:

What do you mean by get the results as 1 value? The SQL you describe implies you want a results set containing 2 values, one from each sum...

I think, _dobbo_, that you've misunderstood the exact meaning of the UNION clause.

[_dobbo_]

jimothy said:

What do you mean by get the results as 1 value? The SQL you describe implies you want a results set containing 2 values, one from each sum...

We need the result of the two queries combined and returned as a single value - we could just return the two and combine them in Cold fusion but we want it done in SQL

I know the union clause combines two result sets, hence wasn't likely to work!

PDV6, your second suggestion, using Liszt's syntax seems to have helped us to get there:

select sum (productval) from producttable where (productnumber=17) or (productnumber!=17 and dateexpires>getdate())

by using this, a bit of extra encapsulation with () in a couple of places has got this working!

Thanks for all the help!