I need to sum SQL table fields using where condtion

Question

0.00/5 (No votes)

See more:

My table value is

Group    Amount
A Group I  1500
A Group II 2000
B Group I  2500
B Group II 500

Expected Output

Group     Amount
A Group I    3500
B Group I    3000

How to do this?

What I have tried:

Im using

SQL

Select Group,Sum(Amount)
From tblAmount
Group by Group

Posted 27-Mar-19 1:09am

Flower@12

Updated 27-Mar-19 2:02am

Maciej Los

v4

Add a Solution

2 solutions

Solution 1

Since you're aggregating (summing) the Amount, you shouldn't include it in the GROUP BY clause.

SQL

SELECT   [Group], 
         SUM([Amount]) AS SumAmount 
FROM     [dbo].[tblAmount] 
WHERE [Group] LIKE '%Group I'
GROUP BY [Group]

BTW, there is no WHERE class involved based on your expected output

Posted 27-Mar-19 1:23am

#realJSOP

Updated 27-Mar-19 1:44am

v2

Comments

[no name] 27-Mar-19 7:32am

i tried it sir but its not working

#realJSOP 27-Mar-19 7:42am

"Is not working" doesn't tell me a damn thing.

#realJSOP 27-Mar-19 7:44am

I changed my answer. try it now.

[no name] 27-Mar-19 7:46am

ok thank you

#realJSOP 27-Mar-19 7:45am

And for the record, you should NEVER name your tables, views, columns and other stuff using SQL reserved words. "Group" is NOT a good name.

[no name] 27-Mar-19 7:48am

i'm showing just sample field name like my table filed name

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 · 2019-03-27T02:03:00

Solution 2

What you're trying to achieve is called "group by part of string"

SELECT   [Group], SUM([Amount]) AS SumAmount 
FROM     [dbo].[tblAmount] 
GROUP BY LEFT([Group], CHARINDEX('Group', [Group]+5)

Note, that below statement:

LEFT([Group], CHARINDEX('Group', [Group]+5)

searches for string "Group" in a [Group] column and returns: {"A Group", "B Group"}

Posted 27-Mar-19 2:03am

Maciej Los

Comments

[no name] 27-Mar-19 8:47am

It's Working Fine.Thank you sir

Maciej Los 27-Mar-19 9:16am

You're very welcome.