How to merge all rows and then sum of all columns in sqlserver

Question

0.00/5 (No votes)

See more:

I have two tables Fee_Assign_to_Students and Fee_Assign_Waiver_to_Students and both tables have columns like

S_Adm_No | FHeadId | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar

I want merge all rows and then sum of all month columns separately.

My code is mentioned below

Fee_Assign_to_Students has value as

S_Adm_No | FHeadId | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar
1001     |    1    | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100
1001     |    2    | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100
1001     |    3    | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100

Fee_Assign_Waiver_to_Students has value as

S_Adm_No | FHeadId | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar
1001     |    1    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50
1001     |    2    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50
1001     |    3    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50

after subtraction from Fee_Assign_to_Students through sql querry. It comes like

S_Adm_No | FHeadId | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar
1001     |    1    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50
1001     |    2    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50
1001     |    3    | 50  | 50  |  50 |  50 |  50 |  50 | 50  |  50 |  50 | 50  |  50 | 50

But I want like

S_Adm_No | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar
1001     | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150 | 150

please any one help me

my code are below..

What I have tried:

SQL

SELECT FA.S_Adm_No,FA.[FHeadId],
FA.APR - ISNULL(FET.APR,0) AS APR,
FA.MAY - ISNULL(FET.MAY,0) AS MAY,
FA.JUN - ISNULL(FET.JUN,0) AS JUN,
FA.JUL - ISNULL(FET.JUL,0) AS JUL,
FA.AUG - ISNULL(FET.AUG,0) AS AUG,
FA.Sept - ISNULL(FET.SEP,0) AS SEP,
FA.OCT - ISNULL(FET.OCT,0) AS OCT,
FA.NOV - ISNULL(FET.NOV,0) AS NOV,
FA.DEC - ISNULL(FET.DEC,0) AS DEC,
FA.JAN - ISNULL(FET.JAN,0) AS JAN,
FA.FEB - ISNULL(FET.FEB,0) AS FEB,
FA.MAR - ISNULL(FET.MAR,0) AS MAR
FROM Fee_Assign_to_Students FA LEFT JOIN  
							 (SELECT FE.S_Adm_No,FE.[FHeadId],
									 SUM(FE.E_Apr) AS APR,
									 SUM(FE.E_MAY) AS MAY,
									 SUM(FE.E_JUN) AS JUN,
									 SUM(FE.E_JUL) AS JUL,
									 SUM(FE.E_AUG) AS AUG,
									 SUM(FE.E_SEP) AS SEP,
									 SUM(FE.E_OCT) AS OCT,
									 SUM(FE.E_NOV) AS NOV,
									 SUM(FE.E_DEC) AS DEC,
									 SUM(FE.E_JAN) AS JAN,
									 SUM(FE.E_FEB) AS FEB,
									 SUM(FE.E_MAR) AS MAR 
							FROM Fee_Assign_Waiver_to_Students FE 
							GROUP BY  FE.S_Adm_No,FE.FHeadId) 
							AS FET ON (FA.S_Adm_No=FET.S_Adm_No) AND (FA.FHeadId=FET.FHeadId)WHERE FA.S_Adm_No='1001'

Posted 23-Aug-16 19:32pm

Member 12245539

Updated 23-Aug-16 20:29pm

Maciej Los

v2

Add a Solution

1 solution

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)

Maciej Los · Accepted Answer · 2016-08-23T20:28:00

I already answered to your similar question here: How to make pivot table in sqlserver[^]

On the first look - wrong approach! The structure of both tables seems to be the same. Why?

Due to the type of join a result set may differ. Please, read this: Visual Representation of SQL Joins[^].

I'd use UNION ALL:

SQL

SELECT T.S_Adm_No,  SUM(T.Apr) AS Apr, SUM(T.May) AS May, SUM(T.Jun) As Jun, SUM(T.Jul) As Jul, SUM(T.Aug) AS Aug, SUM(T.Sep) As Sep, 
     SUM(T.Oct) AS Oct, SUM(T.Nov) AS Nov, SUM(T.Dec) AS Dec, SUM(T.Jan) AS Jan, SUM(T.Feb) AS Feb, SUM(T.Mar) AS Mar
FROM (
SELECT FAS.*
FROM Fee_Assign_to_Students AS FAS 
UNION ALL 
SELECT FAW.*
FROM Fee_Assign_Waiver_to_Students AS FAW
) AS T
GROUP BY T.S_Adm_No