problem with UNION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem with UNION

Author

Topic

sopan
Starting Member

3 Posts

Posted - 2007-05-17 : 13:13:26

I got this Question:
Consider a database table 'employee_salary'. Table schema is as follows -

CREATE TABLE employee_salary(
employeename varchar(20),
salary integer
);

The inserted data are as follows -

Employeename | salary
-------------------|--------
a | 2300
b | 1000
c | 3500
d | 3100
e | 2000

Your objective is to construct a SINGLE QUERY which will produce the following output -

employeename | salary | percent
-------------------|--------|-----------
a | 2300 | 19.08
b | 1000 | 16.03
c | 3500 | 11.45
d | 3100 | 38.17
e | 2000 | 15.27
total | 11900 | 100.00

The last row of the output indicates the total sum of salaries. The column 'percent' indicates the percentage of the associated employees' salary out of the total salary.
My answer is:
select employeename, salary ,(select (100*salary /sum(salary) ) from employee_salary ) as 'percent' from employee_salary
union
select ('total', sum(salary) ,'100.00');

is it correct?

Kristen
Test

22859 Posts

Posted - 2007-05-17 : 13:41:16

Its close!

You can't use "sum(salary)" unless you have an Aggregate Operator

I think you should use UNION ALL instead of "UNION".

You "Union" query is missing the table.

Kristen

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-17 : 14:00:11

More like this:


select employeename, salary , salary/(select convert(Decimal(10,2),sum(salary)) from employee_salary ) * 100 as 'percent' 
from employee_salary 
union all
select 'total', sum(salary) ,'100.00' from employee_salary

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-05-17 : 14:10:11

Well I wasn't actually going to do his/her homework for him/her, particularly as s/he had already made a very good first stab at the answer ...

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-17 : 14:12:12

Sorry, I didnt see he is a new member...
It was too easy to resist ..

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-05-17 : 14:14:22

That's the trouble with your MVPs, always providing Solutions

sopan
Starting Member

3 Posts

Posted - 2007-05-17 : 14:52:03

thanks a lot Kristen!
and u too, Dinakar.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-05-17 : 20:25:48

Aren't we forgetting an order by here? No guarantees the total will come out last.
You might want to have a dummy column or employee name in there.

sopan
Starting Member

3 Posts

Posted - 2007-05-18 : 00:03:12

I tried this at last:
select es.employeename as employeename,
es.salary as salary ,
es.salary/(select convert(Decimal(10,2),sum(salary)) from employee_salary ) * 100 as â€˜percentâ€™ from employee_salary es
union all
select 'total' as employeename , sum(salary) as salary,'100.00' as 'percent' from employee_salary

Kristen
Test

22859 Posts

Posted - 2007-05-19 : 06:56:26

"Aren't we forgetting an order by here? No guarantees the total will come out last."

I did think about that, but I think with a UNION ALL they will be ordered within their respective sets - but I have no proof of that!

Sopan's query is going to get messy including a "SetID" column for sorting, and then a nested-subselect to get rid of the SetID column in the output ...

Would a COMPUTE do instead for the Total?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources