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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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.