Please help in SQL query sum of multiples row

Question

1.00/5 (1 vote)

See more:

My SQL Query

SELECT DISTINCT  ssm.stid,fm.classid,value,fm.Amount FROM [bitcrj_database].[StudentSubMaster] as ssm  inner join  [bitcrj_database].[tbfeemaster] as fm on ssm.classid=fm.classid 
CROSS APPLY [dbo].[fn_split_string](ssm.subjectid,',')

Output

stid	classid	value	Amount
BITC202130	13	     13	1000
BITC202130	13	      7	1000
BITC202130	13	      9	1000

i want to sum of Amount

What I have tried:

i want to sum of Amount

Posted 17-Oct-21 21:26pm

Nishant.Chauhan80

Updated 18-Oct-21 6:44am

Add a Solution

Comments

CHill60 19-Oct-21 5:07am

Your requirement is not clear (based on your comment to solution 1). Try supplying sample data for both [StudentSubMaster] and [tbfeemaster] and then give us an example of what you would expect the results to be for that sample data.
One comment I will is that you are clearly storing the subjects as a comma-separated list in a single column. That is a bad design and leads to clunky code and the problems you are currently having.
Store subject to student relationships in a "linking" table - that way you will not be constrained in the number of subjects by the length of the column you chose up front

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)

Member 15329613 · Accepted Answer · 2021-10-18T06:44:00

Solution 1

One approach would be:

SQL

SELECT SUM(x.amount) AS total_amount
FROM (
  SELECT DISTINCT  ssm.stid,fm.classid,value,fm.Amount FROM [bitcrj_database]. 
   [StudentSubMaster] as ssm  inner join  [bitcrj_database].[tbfeemaster] as fm on ssm.classid=fm.classid 
  CROSS APPLY [dbo].[fn_split_string](ssm.subjectid,',')
) x

Posted 18-Oct-21 6:44am

Member 15329613

Comments

Nishant.Chauhan80 19-Oct-21 0:13am

Thank you so much sir but i want to stid and classid columns with sum of amount

Member 15329613 19-Oct-21 7:08am

Then you use group by and add those columns into the select.

Herman<T>.Instance 19-Oct-21 8:16am

https://www.w3schools.com/sql/