select distict sum of same group in SQL using query.

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

hi friend i have a table having colume grp and marks i want to get sum of marks from table but the condition is that if the grp is same then it count single rows let see the table..

Table tbgrp

grp marks
1 5
2 4
2 4
3 5
3 5
3 5

i using the query select sum(marks) from tbgrp. but it shows the result 28, in real count is 14 because 5 is in 1 group and 4 is in 2 group and 5 is in 3 group so 5+4+5=14 but the result is 28.

plz help me i am very confuse how to get it.

Thanks in advace

Parveen Rathi

Posted 27-Apr-11 3:04am

Parveen Rathi

Add a Solution

2 solutions

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)

Albin Abel · Accepted Answer · 2011-04-27T05:07:00

Solution 3

Hi Parveen Rathi

Use this query

SQL

SELECT Sum(marks) FROM (SELECT  grp,marks,ROW_NUMBER() OVER
    (PARTITION BY grp ORDER BY grp) AS RowNumber
FROM  Table2   ) as  RankedTable  WHERE RowNumber=1

* Use your table name in the place of Table2

If the marks in the group always same then simply use an Avg aggregate or else use the above query.

Posted 27-Apr-11 5:07am

Albin Abel

Updated 27-Apr-11 5:10am

v2

Comments

Sandeep Mewara 27-Apr-11 11:31am

Now here I get the chance to 5++++! ++++ was for conversation below! :)

Albin Abel 27-Apr-11 11:47am

Thanks Sandeep

nit_singh 27-Apr-11 11:42am

grp marks (1 5) (2 6) (2 4) (3 5) (3 5) (3 5) What will be the output from the above query. Now swap the data like that ----grp marks (1 5) (2 4) (2 6) (3 5) (3 5) (3 5) And now what will be output? If you check data is not changed only the index of the data is changed. Now try...;) Every time the result wil be different.

Albin Abel 27-Apr-11 11:55am

Supposed to be. OP wants to select one row in each group. You have (2 6) (2 4) in one set and (2 4) (2 6) on the other. 6 will be selected in the first query and 4 will be selected in the second query. "that if the grp is same then it count single rows" this is what OP told. Single row can be nth row which can be accomplished by changing the RowNumber=n for each group. But then your distinct will select both (2 6) and (2 4) in to account. If OP has a condition that all values inside the group are same why can't OP simply use a average aggregate instead of all complex thinking.

nit_singh 27-Apr-11 14:34pm

:D...yes you are right dear...But as per your comments "Why you made a assumption like that, the table may have other columns or the marks may not similar inside a group.". So why are you assuming like that???right?

Parveen Rathi 28-Apr-11 0:37am

Thanks Albin Abel your query work successfully. Thankyou so much

Parveen Rathi

Albin Abel 28-Apr-11 0:46am

Thanks Parveen Rathi. But me and nit_singh have question that in a single group the marks will be same or different. If different what is your criteria to select. If my query it selects the first row in a group.

nit_singh · Accepted Answer · 2011-04-27T03:40:00

Solution 2

with distinctTable as
(
select distinct * from tbgrp
)
select sum(marks) from distinctTable

Posted 27-Apr-11 3:40am

nit_singh

Comments

Sandeep Mewara 27-Apr-11 9:43am

My 5!

nit_singh 27-Apr-11 9:54am

Thanks Sandeep

Albin Abel 27-Apr-11 11:09am

Hi nit_singh have you checked your query?. It won't work. You know why you are using distinct * which select all the rows because all the row combination is unique with all rows.

Sandeep Mewara 27-Apr-11 11:12am

Hmmm... good catch Albin. 5 vitual upvote.

My vote here re-considered to 3 for now. Thanks mate.

Albin Abel 27-Apr-11 11:18am

Hi Sandeep, no problem. Though it doesn't work it is a good try. I upvote your 3 to 5. :)

Sandeep Mewara 27-Apr-11 11:21am

Great man! :)

Surely a good try it was. Will keep a watch on it for now and will defintily revote it after you and nit_singh discuss it on ;)

nit_singh 27-Apr-11 11:21am

@Sandeep-You can also try..:)

Sandeep Mewara 27-Apr-11 11:23am

:)
3 -> 4 Done! Good effort... Keep up the good work! :)

not 5 because of that assumption but would not blame you as the question is framed like that!

nit_singh 27-Apr-11 11:29am

yes correct.. :)

nit_singh 27-Apr-11 11:19am

No Albin, See the data in the table, If you notice combination of grp and marks is similar for every group. If the combination is different then this query will not work. I know your solution is also correct, but this solution will also work. You can try...:)

Albin Abel 27-Apr-11 11:46am

You asked me to try. I tried and got answer 28. I told already why it is. As per your words.."If you notice combination of grp and marks is similar for every group" Why you made a assumption like that, the table may have other columns or the marks may not similar inside a group. I suggested that option also in my answer, if the marks are same inside the group simply average would work. In that case jayantbramhankar's answer is simple enough where replace the sum with avg (average). As OP didn't given that condition the marks inside the group always same then I simply can't assume it.

nit_singh 27-Apr-11 11:50am

The query posted by you will also not work, because if you swap the data, you will get different answers every time.. ;)

Parveen Rathi 28-Apr-11 0:41am

Thanks nit_singh your query also work successfully.

Parvee Rathi

nit_singh 28-Apr-11 1:01am

Thanks Praveen...