UNION Query is not working properly

Question

0.00/5 (No votes)

See more:

I am using two table's. i want to sum in both tables using union.

When i am executing the query i am getting error.

see my table and query below.

Table1

EmpNumber value WeekPeriod YearPeriod
101 1 1 2012
101 1 2 2012
101 0 3 2012
102 1 1 2012
102 1 2 2012
102 0 3 2012
103 1 1 2012
103 1 2 2012
103 0 3 2012

Table2

EmpNumber value WeekPeriod YearPeriod
101 1 1 2012
101 1 2 2012
101 0 3 2012
201 1 1 2012
201 1 2 2012
201 0 3 2012
202 1 1 2012
202 1 2 2012
202 0 3 2012
203 1 1 2012
203 1 2 2012
203 0 3 2012

Query

SQL

Select sum(val) as Val from 
(select * from Table2 T1 inner join project P on T1.projectcode = P.Projectno 
where T1.WeekPeriod =16 and T1.YearPeriod =2012 and T1.Emp_Number ='101'  
union
select * from Table2 T2 inner join project P on T2.projectcode = P.Projectno 
where T2.WeekPeriod =16 and T2.YearPeriod =2012 and T2.Emp_Number ='101'  ) Val

Posted 19-Apr-12 23:00pm

gani7787

Updated 19-Apr-12 23:02pm

Prasad_Kulkarni

v2

Add a Solution

2 solutions

Solution 2

Your query is correct. You might have used wrong column name, please check.
If still problem persist post your table structure too. I have created a sample query use this for your reference

SQL

DECLARE @Table1 TABLE (EmpNumber VARCHAR(20), value INT, WeekPeriod INT, YearPeriod INT)
INSERT INTO @Table1 VALUES 
('101',1,1,2012),('101',1,2,2012),('101',0,3,2012),
('102',1,1,2012),('102',1,2,2012),('102',0,3,2012),
('103',1,1,2012),('103',1,2,2012),('103',0,3,2012)

DECLARE @Table2 TABLE (EmpNumber VARCHAR(20), value INT, WeekPeriod INT, YearPeriod INT)
INSERT INTO @Table1 VALUES 
('101',1,1,2012),('101',1,2,2012),('101',0,3,2012),
('201',1,1,2012),('201',1,2,2012),('201',0,3,2012),
('202',1,1,2012),('202',1,2,2012),('202',0,3,2012),
('203',1,1,2012),('203',1,2,2012),('203',0,3,2012)

SELECT 
	SUM(ISNULL(value,0)) AS Val 
FROM 
	(
		SELECT 
			* 
		FROM 
			@Table1 T1  
		WHERE 
			T1.EmpNumber ='101'  
		UNION
		SELECT 
			* 
		FROM 
			@Table2 T2  
		WHERE 
			T2.EmpNumber = '101'  
	) Val

Regards
Praveen

Posted 19-Apr-12 23:25pm

Praveen Meghwal

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*Karthik Harve* · Accepted Answer · 2012-04-19T23:13:00

Hi,
Try by replacing " * " in your query to the specific column names using the table aliases. as like below

SQL

Select sum(value) as Val from
(select T1.value from Table2 T1 inner join project P on T1.projectcode = P.Projectno
where T1.WeekPeriod =16 and T1.YearPeriod =2012 and T1.Emp_Number ='101'
union
select T2.value from Table2 T2 inner join project P on T2.projectcode = P.Projectno
where T2.WeekPeriod =16 and T2.YearPeriod =2012 and T2.Emp_Number ='101'  )

Hope it works.

UNION Query is not working properly

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

UNION Query is not working properly

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us