Skip to content
Advertisement

Use Aggregate Function in UNION ALL result set

How can I use aggregate Functions in UNION ALL Resultset

FOR EXAMPLE

SELECT A,B FROM MyTable
UNION ALL
SELECT B,C FROM MYAnotherTable

Result Set Would Be

    A  B
--------------
    1  2
    3  4
    4  5
    6  7

When I tried to get MAX(A) it returns 3. I want 6.

When I tried to get MAX(B) it returns 4. I want 7.

Other than Max(), Can I get another aggregate function which user defined?

For example:

(SELECT TOP 1 A WHERE B=5)

Real Case Here

Advertisement

Answer

Try this way:

select max(A)
from(
      SELECT A,B FROM MyTable
      UNION ALL
      SELECT B,C FROM MYAnotherTable
    ) Tab

SQL Fiddle DEMO

If the column A is varchar (You said that in the comment below) try this way:

select max(A)
from(
      SELECT cast(A as int) as A,B FROM MyTable
      UNION ALL
      SELECT B,C FROM MYAnotherTable
    ) Tab

With TOP 1

select max(A)
from(
      SELECT top 1 cast(A as int) as A,B FROM MyTable
      UNION ALL
      SELECT B,C FROM MYAnotherTable
    ) Tab
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement