hello Guys,

I facing a sql query problem which is i need to join many query into one query. That is :

a)

SELECT sum(Total) As [MONEY1]
FROM
((SELECT SUM(T1.Amount+T2.Amount) AS Total FROM TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID) UNION ALL (SELECT SUM(T3.Amount+T4.Amount) AS Total FROM TABLE3 T3 INNER TABLE4 T4 ON T3.ID = T4.ID) ) DERIVETBL

b)

Select sum(AllTotal) as [MONEY2]
(SELECT SUM(T5.Total+T6.Total) AS ALLTotal FROM TABLE1 T5 INNER TABLE6 T6 ON T5.ID = T6.ID) UNION ALL 
(SELECT SUM(T7.Total+T8.Total) AS ALLTotal FROM TABLE7 T7 INNER TABLE8 T8 ON T7.ID = T8.ID ) DERIVETBL

My Question is how i can combine (a) query and (b) query into into query, something like : SELECT 'Testing' as [Title] , [MONEY1] , [MONEY2]

Recommended Answers

All 3 Replies

First SQL Query
UNION
Second SQL Query

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
SQL UNION Syntax

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.
SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.
SQL UNION Example

Look at the following tables:

"Employees_Norway":
E_ID E_Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

"Employees_USA":
E_ID E_Name
01 Turner, Sally
02 Kent, Clark
03 Svendson, Stephen
04 Scott, Stephen

Now we want to list all the different employees in Norway and USA.

We use the following SELECT statement:

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

The result-set will look like this:
E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen

The purpose of the SQL UNION command is to combine the results of two queries together. In this respect, UNION is somewhat similar to JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need to be of the same data type. Also, when using UNION, only distinct values are selected (similar to SELECT DISTINCT).

The syntax is as follows:

[SQL Statement 1]
UNION
[SQL Statement 2]

Say we have the following two tables,

Table Store_Information
store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999

Table Internet_Sales
Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
Jan-12-1999 $750

and we want to find out all the dates where there is a sales transaction. To do so, we use the following SQL statement:

SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

Result:
Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999

Try this query below:

SELECT 'Testing' as [Title] , [MONEY1] , [MONEY2] 
  from (SELECT sum(Total) AS [MONEY1] FROM((SELECT SUM(T1.Amount+T2.Amount) AS Total FROM TABLE1 T1 INNER TABLE2 T2 ON T1.ID = T2.ID) UNION ALL (SELECT SUM(T3.Amount+T4.Amount) AS Total FROM TABLE3 T3 INNER TABLE4 T4 ON T3.ID = T4.ID) ) DERIVETBL) X1, (SELECT sum(AllTotal) AS [MONEY2](SELECT SUM(T5.Total+T6.Total) AS ALLTotal FROM TABLE1 T5 INNER TABLE6 T6 ON T5.ID = T6.ID) UNION ALL (SELECT SUM(T7.Total+T8.Total) AS ALLTotal FROM TABLE7 T7 INNER TABLE8 T8 ON T7.ID = T8.ID ) DERIVETBL) X2
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.