15,036,823 members
5.00/5 (1 vote)
See more:
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

## 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.
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.

## Solution 2

```with distinctTable as
(
select distinct * from tbgrp
)
select sum(marks) from distinctTable```
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...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 170 Richard MacCutchan 80 CPallini 80 Dave Kreskowiak 50 Patrice T 50
 OriginalGriff 3,055 Richard Deeming 1,823 Richard MacCutchan 1,645 CPallini 1,083 Dave Kreskowiak 781

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900