Sum row values in sql server with group by

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi Frnds,
I have a Table With following details,

EmpID          CashID     CashName      Incentives       Reason

  1               1         Travel        1000        Bangalore trip
  1               1         Travel        2000        Chennai trip
  1               2         Food           500        June Month
  1               2         Food           900        July Month
  2               1         Travel        5000        Chennai
  2               2         Food          3000        Delhi
  1               3         Staying       8000        Chennai
  2               3         Staying       2000        Delhi
  2               3         Staying       1000        Delhi

I need to Combine rows as per Empid and CashId...I need Output as follows

EmpID        CashId         CashName       Incentives
1             1              Travel          3000
1             2              Food            1400    
1             3              Staying         8000

I have tried with group by and sum.

Posted 8-Jul-13 21:29pm

itsureshuk

Updated 8-Jul-13 21:32pm

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 · 2013-07-08T21:33:00

Solution 1

Try this:

SQL

SELECT EmpID, CashID, CashName, SUM(Incentives) AS SumOfIncentives
FROM YourTableName
GROUP BY EmpID, CashID, CashName

Example for total SUM:

SQL

DECLARE @tmp TABLE (A INT, B INT, C VARCHAR(10), D INT)

INSERT INTO @tmp (A, B, C, D)
SELECT 1 AS A, 2 AS B, 'A' AS C, 2 AS D
UNION ALL
SELECT 1 AS A, 2 AS B, 'A' AS C, 3 AS D
UNION ALL
SELECT 2 AS A, 3 AS B, 'B' AS C, 4 AS D
UNION ALL
SELECT 2 AS A, 3 AS B, 'B' AS C, 5 AS D

SELECT A, B, C, SUM(D) AS SumOfD
FROM @tmp
GROUP BY A,B,C
UNION ALL
SELECT 0 AS A, 0 AS B, 'Total: ' AS C, SUM(D) AS SumOfD
FROM @tmp

Posted 8-Jul-13 21:33pm

Maciej Los

Updated 9-Jul-13 1:07am

v3

Comments

itsureshuk 9-Jul-13 5:47am

if i want one more column as overall total what i have to do

Maciej Los 9-Jul-13 6:47am

It is possible, but, you need to tell me which rows/columns do you want to sum as overall.

Maciej Los 9-Jul-13 7:07am

See my answer now ;)

itsureshuk 10-Jul-13 4:38am

ya your query is working.Thanks...Can u suggest me good websties for learning subqueries , case , triggers, joins using for subqueries...

Maciej Los 10-Jul-13 6:40am

Of course, i can:
T-SQL Reference on MSDN[^]
T-SQL tutorial[^]
T-SQL on w3schools.com[^]
But the best way is to buy a book ;)

itsureshuk 10-Jul-13 7:59am

ok...Thanks,,,I will go through it...