How to sum total when join two queries

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi,

I am using two tables and get the o/p.

I have one column called salary in second table.

I want to calculate and sum of Total salary and display in one seperate column with total sum amount like below format.

See my query...

SQL

select T1.Code,T1.Serv.No,T2.ID,T2.Salary,sum(T2.Salary) asTotalSum from Table1 T1 left outer joinTable2 T2 on
T1.EmpCode=T2.EmpCode and T2.EmpNo= 'E001' group by T1.Code,T1.Serv.No,T2.ID,T2.Salary order
by T1.Code,T1.Serv.No,T2.ID,T2.Salary

Output
----------

Table 1 (T1)		Table2 (T2)		<br />
Code	Serv.No	         ID	Salary	Total<br />
F94134	PI4235INS	4235	NULL	2500 (Total Salary)<br />
F94150	PI4211PRO	4211	300	<br />
F94133	PI4234ELE	4234	900	<br />
F94135	PI4211PIP	4211	NULL	<br />
F94131	PI4237CIV	4237	800	<br />
F94120	PI3010PRT	3020	NULL	<br />
F94150	PI4223COM	4233	500	<br />
F94130	PI4221ENG	4221	NULL	<br />
F94150	PI4278ENV	4278	NULL	<br />
F94132	PI4211OST	4211	NULL	<br />
F94021	PI4010MPC	4010	NULL	<br />
F94030	PI1350QUA	1350	NULL

I want to display in the above format in Total Column Sum.
How to do this..
.

Posted 23-Dec-11 0:13am

gani7787

Updated 23-Dec-11 1:50am

AmitGajjar

v5

Add a Solution

Comments

Rajesh Anuhya 23-Dec-11 6:14am

Not clear..

3 solutions

Solution 1

Refer this

sum of Total working hours

Thanks..

Posted 23-Dec-11 0:21am

Shobana16

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)

Rajesh Anuhya · Accepted Answer · 2011-12-23T01:46:00

Solution 2

go through the below links

ROLLUP[^]

CUBE[^]

Posted 23-Dec-11 1:46am

Rajesh Anuhya

Comments

RaisKazi 23-Dec-11 8:34am

My 5.

**AmitGajjar** · Accepted Answer · 2011-12-23T01:50:00

Hi,

if you are using SQL server 2008 , you can use Rollup to get Total of your query.

SQL

select T1.Code,T1.Serv.No,T2.ID,T2.Salary,sum(T2.Salary) asTotalSum from Table1 T1 left outer joinTable2 T2 on
T1.EmpCode=T2.EmpCode and T2.EmpNo= 'E001' group by T1.Code,T1.Serv.No,T2.ID,T2.Salary order
by T1.Code,T1.Serv.No,T2.ID,T2.Salary with rollup

there will be last line with total value.

hope this will help you.

thanks
-amit.