Click here to Skip to main content
15,036,823 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
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

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




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