Ad:

MS SQL Discussion Thread
Marked Solved
Views: 2877

Nov 13th, 2008

How To use Intersect

Expand Post »

Hi Guys,

I have some confusing part in the sql command

the question is
i have 2 different query :
a)

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

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.

Similar Threads

Array/String intersect and Array/Array intersect code in C#
Asking for help in very simple Python in Python
class rectangle in Java
dot game in Geeks' Lounge
Heap problem at run time in C++

nokomoli

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

26 posts
since Nov 2007

Permalink

Nov 13th, 2008

Re: How To use Intersect

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

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

Permalink

Nov 13th, 2008

Re: How To use Intersect

HI Huang Zhi,

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

nokomoli

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

26 posts
since Nov 2007

Permalink

Nov 14th, 2008

Re: How To use Intersect

Click to Expand / Collapse Quote originally posted by huangzhi ...

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 :

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.

nokomoli

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

26 posts
since Nov 2007

Permalink

Nov 15th, 2008

Re: How To use Intersect

Use this command

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

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

huangzhi

Reputation Points: 10

Solved Threads: 13

Light Poster

Offline

48 posts
since Feb 2008

Permalink

Nov 17th, 2008

Re: How To use Intersect

Click to Expand / Collapse Quote originally posted by huangzhi ...

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

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.

nokomoli

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

26 posts
since Nov 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Radio button Value with SQL

Next Thread in MS SQL Forum Timeline: the difference between Stored procedures temporary tables

DaniWeb Message

Cancel Changes

User Name
Password