Member Avatar for nokomoli

Hi Guys,

I have some confusing part in the sql command

the question is
i have 2 different query :
a)

Select SUM(T1.Amount+T2.Amount) as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID

b)

Select Sum(Amount) as Total FROM TABLE3

the result if a) query is 100 and result of b) query is 50
But i wanna show the result is 150 (That means add query a) result and query b result.)
So what should i do?

I use INTERSECT to combine both query, but it shows "syntax error"
have any one to help me?

  • 2 Contributors
  • 5 Replies
  • 171 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by nokomoli
Member Avatar for huangzhi 
select sum(Total)
  from 
    (
    select T1.Amount+T2.Amount as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID
    union all
    select Amount as Total FROM TABLE3 
    ) X
Member Avatar for nokomoli

HI Huang Zhi,

Thanks your help you.
This statement is work!!!

Member Avatar for nokomoli 
select sum(Total)
  from 
    (
    select T1.Amount+T2.Amount as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID
    union all
    select Amount as Total FROM TABLE3 
    ) X

Really thanks for ur help. :)
But Now i have new question about this
refered by to ur answer, if let say i wanna to add condition inside this statement, is it possible?
cox when i try :

select sum(Total) as [Amount]
  from 
    (
    select T1.Amount+T2.Amount as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID
    union all
    select Amount as Total FROM TABLE3 
    ) X
Where Amount is not null

That is because i don't want to view a result with result is null value.

Member Avatar for huangzhi

Use this command

select sum(Total) as [Amount]
  from 
    (
    select T1.Amount+T2.Amount as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID
    union all
    select Amount as Total FROM TABLE3 Where Amount is not null
    ) X

or

select sum(Total) as [Amount]
  from 
    (
    select T1.Amount+T2.Amount as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID
    union all
    select isNull(Amount, 0) as Total FROM TABLE3    
  ) X
Member Avatar for nokomoli 
select sum(Total)
  from 
    (
    select T1.Amount+T2.Amount as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID
    union all
    select Amount as Total FROM TABLE3 
    ) X

Thanks Help but i think u misunderstood my question. anyway i had found out the answer

select sum(Total) as [Total Amount]
  from 
    (
    select T1.Amount+T2.Amount as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID
    union all
    select Amount as Total FROM TABLE3 
    ) X Having (sum(Total)> 0)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.