Get sum of values to another row in a group in SQL.

Question

0.00/5 (No votes)

See more:

I have a table Parameter_List with fields Parameter_ID,Parameters_Name, Target_Value, GroupID.
The values can be

Parameter_ID    Parameters_Name Target_Value    GroupID Sequence_No
1   Para_1  1000    2   1
2   Para_2  2000    2   2
3   Para_3  3000    2   3
4   Para_4  NULL    2   4
5   Para_5  5000    2   5

6   Para_1  1450    3   1
7   Para_2  1200    3   2
8   Para_4  NULL    3   3
9   Para_5  3000    3   4

10  Para_2  3000    4   1
11  Para_3  4000    4   2
12  Para_4  NULL    4   3

As you can see, Target_Value for Para_4 is always NULL.
Now when I want to display the records, I want the value for Para_4 as the sum of Target_Value of the parameteres above Para_4 for that Group.
For example, for GroupID 2, value of Para_4 should be sum of first 3 rows. Table has a Sequence_No column. So Para_4 should have Target_Value as a sum of all rows above it in that specific group.
It should group by GroupID. So the result would look like

Para_1  1000    2
Para_2  2000    2
Para_3  3000    2
Para_4  6000    2
Para_5  5000    2

Para_1  1450    3
Para_2  1200    3
Para_4  2650    3
Para_5  3000    3

What I have tried:

I tried to use UNION but I don't think it is a good solution (or is it?). How does it affect the performance?

Posted 21-May-18 18:54pm

Lokesh Zende

Updated 24-May-18 19:49pm

Mohibur Rashid

v2

Add a Solution

2 solutions

Solution 2

Declare @table table(Parameter_ID int,Parameters_Name varchar(10),Target_Value int,GroupID int,Sequence_No int)
insert into @table values
(1,   'Para_1',  1000,    2,   1),
(2,   'Para_2',  2000,    2,   2),
(3,   'Para_3',  3000,    2,   3),
(4,   'Para_4',  NULL,    2,   4),
(5,   'Para_5',  5000,    2,   5),
								 
(6,   'Para_1',  1450,    3,   1),
(7,   'Para_2',  1200,    3,   2),
(8,   'Para_4',  NULL,    3,   3),
(9,   'Para_5',  3000,    3,   4),
								 
(10,  'Para_2',  3000,    4,   1),
(11,  'Para_3',  4000,    4,   2),
(12,  'Para_4',  NULL,    4,   3)

select *,sum(Target_Value)over(partition by GroupID order by Sequence_No)Tot from @table order by Parameter_ID

Posted 24-May-18 19:51pm

kirthiga S

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 · 2018-05-21T20:24:00

It's possible to achieve by using below sql statement:

SQL

SELECT T.Parameters_Name, SUM(T.Target_Value) AS Target_Value, T.GroupID
FROM (
  SELECT 'Para_4' AS Parameters_Name, Target_Value, GroupID
  FROM Tmp
  WHERE Parameters_Name IN ('Para_1', 'Para_2', 'Para_3')
  UNION ALL
  SELECT Parameters_Name, Target_Value, GroupID
  FROM Tmp
  WHERE Parameters_Name <> 'Para_4'
) AS T
GROUP BY T.Parameters_Name, T.GroupID

SQL Fiddle[^]

Note: In case when Para_4 is always NULL, a WHERE clause in second SELECT statement is redundant .