add sum at the bottom of the result in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

hi
i have 4 tables

## table 1 ''Employee''

 ID | Name
----|----
 01 | A1
 02 | B1
 03 | C1
 04 | D1

## table 2 ''Location''

 ID | Location
----|----------
 01 | L1
 02 | L1
 03 | L3
 04 | L2

## table 3 ''Department''

 ID | Department
----|------------
 01 | D1
 02 | D1
 03 | D2
 04 | D3

## table 4 ''Salary''

 ID | Salary
----|--------
 01 | 1000
 02 | 2000
 03 | 1500
 04 | 2000

The output should come as

Location  |Department|Salary
----------|----------|-------
   L1     |   D1     |1000
   L1     |   D2     |2000
----------|----------|------
          |  Salary  |3000
----------|----------|-------
   L2     |   D3     |1500
   L2     |   D5     |2000
----------|----------|------
          |  Salary  |3500
----------|--------  |------
        |Total Salary|6500

have to create it in sql

and is it possible to get following format in sql

C#

Location  |Department|Salary
----------|----------|-------
   L1     |   D1     |1000
          |   D2     |2000
----------|----------|------
          |  Salary  |3000
----------|----------|-------
   L2     |   D3     |1500
          |   D5     |2000
----------|----------|------
          |  Salary  |3500
----------|--------  |------
        |Total Salary|6500

Thank you
regards
varun vimal

Posted 27-Jan-16 6:12am

write2varun

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)

Richard Deeming · Accepted Answer · 2016-01-27T06:46:00

Assuming the mistakes in your data are just typos in the question, Grouping Sets[^] will do what you want.

SQL

WITH cteData As
(
    SELECT
        L.Location,
        D.Department,
        S.Salary
    FROM
        Location As L
        INNER JOIN Department As D
        ON D.ID = L.ID
        INNER JOIN Salary As S
        ON S.ID = L.ID
)
SELECT
    Location,
    CASE
        WHEN Location Is Null THEN 'Total Salary'
        WHEN Department Is Null THEN 'Salary'
        ELSE Department
    END As Department,
    Sum(Salary) As Salary
FROM
    cteData
GROUP BY GROUPING SETS
(
    (Location, Department),
    (Location),
    ()
);

Output:

Location    Department    Salary
L1          D1            1000
L1          D2            2000
L1          Salary        3000
L2          D3            1500
L2          D4            2000
L2          Salary        3500
NULL        Total Salary  6500

Any further formatting needs to be done in the UI.