Best Way to calculate totals from query

[brianism]

Could someone point me in the right direction to add up my data and distinctly show it in my query?

I have a table with the following fields:
id, team_id, compname, teamname, totallost

I want to add up the "totallost" row where the "team_id" and "compname" fields are the same...then show the compname with the sum of the totallost once in my table and determine who is winning.

[Kronin555]

select compname, teamname, sum(totallost) as totallost
from table
group by compname, teamname

[brianism]

Thank you for such a quick response. I did try this before, and it does pull out the the sum for each team. How would I only display the team with the highest "totallost"? In the case of my example, I would only want to display the team "The Betters" for the competition "Test Competition" because they have the best score of "55" lost.

[Dan_Bracuk]

Depends on the db. Some have a top n, feature, some have a rank function.

Google "top n your_db_name" for details.

[Kronin555]

select compname, teamname, sum(totallost) as totallost
from table
group by compname, teamname
order by totallost desc
limit 1

This works in PostgreSQL

[brianism]

Thank you for the great help. This code works well, but is there a way to display the highest totallost and differentiate between competition names? My example is for one compname, but the table will have multiple compname's and I want to build a table showing only the highest totallost for each compname.

You guys have been a great help. I learned something new today already.