MS SQL

MS SQL RSS

How To use Intersect

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Nov 2007

Posts: 26

Reputation: nokomoli is an unknown quantity at this point

Solved Threads: 0

nokomoli

Offline

Light Poster

How To use Intersect

Nov 13th, 2008

Hi Guys,

I have some confusing part in the sql command

the question is
i have 2 different query :
a)

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
 SELECT SUM(T1.Amount+T2.Amount) as Total FROM TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID
 Select SUM(T1.Amount+T2.Amount) as Total From TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT Sum(Amount) as Total FROM TABLE3
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?

Last edited by peter_budo; Nov 13th, 2008 at 7:46 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

Re: How To use Intersect

Nov 13th, 2008

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

Hence Wijaya
www.ex-Soft.tk

•

Join Date: Nov 2007

Posts: 26

Reputation: nokomoli is an unknown quantity at this point

Solved Threads: 0

nokomoli

Offline

Light Poster

Re: How To use Intersect

Nov 13th, 2008

HI Huang Zhi,

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

•

Join Date: Nov 2007

Posts: 26

Reputation: nokomoli is an unknown quantity at this point

Solved Threads: 0

nokomoli

Offline

Light Poster

Re: How To use Intersect

Nov 14th, 2008

•

Originally Posted by huangzhi

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)
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
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 :

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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.

Last edited by peter_budo; Nov 17th, 2008 at 3:01 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

Re: How To use Intersect

Nov 15th, 2008

Use this command

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

Hence Wijaya
www.ex-Soft.tk

•

Join Date: Nov 2007

Posts: 26

Reputation: nokomoli is an unknown quantity at this point

Solved Threads: 0

nokomoli

Offline

Light Poster

Re: How To use Intersect

Nov 17th, 2008

•

Originally Posted by huangzhi

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)
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
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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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)
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)

Last edited by peter_budo; Nov 18th, 2008 at 6:27 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.