CODEWITHSUNDEEP
sqlderby
DCR
DCR

Reputation: 15665

derby SQL syntax using union

I'm trying the following:

select sum(a.shares*a.cps) - sum(b.shares*b.cps) from
   (select * from transactions
        where usr  = 1
          and type = "C"
           or type = "S") as a
union
   (select * from transactions
                 where usr = 1
                   and type = "W"
                   or  type = "B") as b

and get back:

Error: Syntax error: Encountered "as" at line 10, column 40.

Upvotes: 0

Views: 285

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

Each SELECT statement within UNION must have the same number of columns. The columns must also have similar data types. The columns in each SELECT statement must also be in the same order.

select col1,col2 from a
union 
select 1,2 from b

but in your query number of column is not same in both query and your query might be like below

select sum(col) from
   ( select shares*cps as col from transactions
    where usr  = 1
     and type = "C"
     or type = "S"        
        union
       select shares*cps*(-1) from transactions
        where usr = 1
        and type = "W"
        or  type = "B"
    ) t

Upvotes: 1

Related Questions