Perform sum from table A where a condition is met and save to Table B

Question

2.00/5 (1 vote)

See more:

Hello and Good day,

I am a new beginner. I have this challenge in C# and would appreciate if anyone can help me out. I have three tables as described belo;
TABLE A
VehN, Dst, TrpM

TABLE B
VehN

TABLE c
VehN, Tot_Dst

TABLE A DATA
VehN Dst TrpM
UYO123CDF, 2000, OCT
PHC456ABU, 150, OCT
ERT678SER, 250, OCT
ERT678SER, 1500, OCT
UYO123CDF, 501, OCT
KUU8376G, 83155, SEP
KUU8376G, 1500, OCT
LAG123EKY, 3100, OCT
KUU8376G, 601, OCT
PHC456ABU, 500, OCT
PHC456ABU, 801, OCT

TABLE B DATA
VehN
UYO123CDF
PHC456ABU
ERT678SER
KUU8376G
LAG123EKY

I want TABLE C to be updated as sum(table-A.Dst) where first data in Table-B = Table-A.VehN and where Table-A.TrpM = 'OCT' This should continue untill Table-B end of file.

Table C will now look like this
VehN, Tot_Dst
UYO123CDF, 2,501
PHC456ABU, 1,451
ERT678SER, 1,750
KUU8376G, 2,101
LAG123EKY, 3,100

I will need a c# code and SQL command that will enable me achieve what I described above.

Olabode

Posted 1-Nov-15 7:36am

Member 11847697

Add a Solution

Comments

Zoltán Zörgő 1-Nov-15 14:35pm

You can use subquery in UPDATE statement. And you can use table aliases to link between WHERE clause in UPDATE and subquery. It is quite simple. But your formulation about the exact situation is not quite clear.

Suvendu Shekhar Giri 1-Nov-15 14:40pm

Everything is ready but have you tried anything so far? If yes , then share the relevant code which you are stuck at.

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)

**Zoltán Zörgő** · Answer 1 · 2015-11-01T08:42:00

Solution 1

Something like this?

SQL

UPDATE TableC c
set Tot_Dst = 
(
SELECT SUM(TableA.Dst) FROM TableA a WHERE a.TrpM="OCT" AND a.VehN = c.VehN
)
where c.VehN in (SELECT VehN from TableB)

This is for T-SQL part. For C# part it depends what do you need to pass from code to T-SQL. You can pass month or even TableB if necessary, but than you need to wrap this in a stored proceudre.

Posted 1-Nov-15 8:42am

Zoltán Zörgő

Updated 1-Nov-15 8:44am

v2

Comments

Member 11847697 2-Nov-15 14:38pm

Thanks Zoltan Zorgo,

When I tried out your suggested solution in SQL Server Management Studio, I got this error message;
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'C'.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'where'

Could you look into it again and advise what could have gone wrong.

Regards.
Olabode

Zoltán Zörgő 2-Nov-15 15:59pm

Hello
It would be helpful if you would post the scipted export of your tables (schema and data) so I can test without having to spend time on rewriting your environment. Please remember that I don't have your database.

But I think the update syntax is different if one needs alias. Try this:


UPDATE c
SET Tot_Dst = 
(
SELECT SUM(TableA.Dst) FROM TableA a WHERE a.TrpM="OCT" AND a.VehN = c.VehN
)
FROM TableC c
WHERE c.VehN in (SELECT VehN from TableB)