Best Way to calculate totals from query

Forum|Forum|17 years ago
December 2, 2008
5 replies
579 views

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.

Advanced techniques

This topic has been closed for replies.

B

brianismAuthor

Inspiring

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.

K

Kronin555

Participating Frequently

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

This works in PostgreSQL

D

Dan_Bracuk

Inspiring

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

Google "top n your_db_name" for details.

B

brianismAuthor

Inspiring

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.

K

Kronin555

Participating Frequently

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

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded