# Thread: Sum with limit and group

1. Registered User
Join Date
Dec 2010
Posts
8

## Unanswered: Sum with limit and group

I have a results table

EventID, Catergory, Placed, TeamName, EventYear, points

('1200','Event1',1,'team1','2009', 250),
('629','Event2',25,'team23,'2010', 125),
('145','Event4',33,'team33,'2007', 75),
('1564','Event5',4,'team38,'2002', 200),
('800','Event8',51,'team13,'2001', 25),
('568','Event1',16,'team63,'2006', 80),
('895','Event3',27,'team123,'2003', 120),
('568','Event7',8,'team153,'2002', 175),
('532','Event4',19,'team3,'2001', 90),
('123','Event11',10,'team83,'2000', 140),

The table has a couple hundred thousand records.
I'm trying to get the the top 10 Placed per team based on an event type and year and sum up the points.

So far I have

Select b.totalpoints, Catergory, b.Teamname
From (Select Placed, Sum(Points) as totalpoints, Catergory, Teamname From Event_Results as a
Where EventYear = '2009'
And Catergory = 'Event1'
Group By Teamname
) as b
ORDER BY totalpoints Desc;

Which works fine but it returns the sum of all points for teams. I can't find a place for Limit 10 to work.

Any thoughts or better solutions greatly appreciated.

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
please run this query, and confirm that it is working correctly to give the top 10 points per team...
Code:
```SELECT t.Teamname
, t.Placed
, t.Points
FROM Event_Results AS t
WHERE Catergory = 'Event1'
AND EventYear = '2009'
AND ( SELECT COUNT(*)
FROM Event_Results
WHERE Teamname = t.Teamname
AND Catergory = 'Event1'
AND EventYear = '2009'
AND Points > t.Points ) < 10
ORDER
BY t.Teamname
, t.Points DESC```
once you've convinced yourself it's working, replace the ORDER BY clause with
Code:
```GROUP
BY t.Teamname```
and then remove t.Placed from the SELECT clause and replace t.Points with SUM(t.Points)

vwalah

3. Registered User
Join Date
Dec 2010
Posts
8

## No records

Thanks,
I'll try it.
Last edited by mpryan73; 12-17-10 at 10:02.

4. Registered User
Join Date
Dec 2010
Posts
8

## server shutdown

Ok I tried and and I'm getting server shutdown in progress error 1053. Other queries work.

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
would you please do a SHOW CREATE TABLE so i can see your indexes

6. Registered User
Join Date
Dec 2010
Posts
8
Is this what your looking for?

'Event_Results', 0, 'PRIMARY', 1, 'ID', 'A', 191725, , '', '', 'BTREE', ''
'Event_Results', 1, 'Index_1', 1, 'EventID', 'A', 1101, , '', 'YES', 'BTREE', ''
'Event_Results', 1, 'Index_1', 2, 'TeamName', 'A', 38345, , '', 'YES', 'BTREE', ''
'Event_Results', 1, 'NumberTeams', 1, 'NumberOfTeams', 'A', 132, , '', 'YES', 'BTREE', ''

7. Registered User
Join Date
Dec 2010
Posts
8
Or this

'Event_Results', 'CREATE TABLE `Event_Results` (
`EventID` varchar(15) default NULL,
`Catergory` varchar(25) default NULL,
`Placed` int(11) default NULL,
`TeamName` varchar(150) default NULL,
`DisplayOrder` int(11) default NULL,
`Points` int(11) default NULL,
`ID` int(11) NOT NULL auto_increment,
`NumberOfTeams` int(11) default NULL,
`EventYear` varchar(4) default NULL,
PRIMARY KEY (`ID`),
KEY `Index_1` USING BTREE (`EventID`,`TeamName`),
KEY `NumberTeams` USING BTREE (`NumberOfTeams`)
) ENGINE=MyISAM AUTO_INCREMENT=195320 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC'

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by mpryan73
Or this
yeah, that

try this --
Code:
```ALTER TABLE Event_Results

9. Registered User
Join Date
Dec 2010
Posts
8

As I don't really under stand your solution. I just want to make sure my wants are clear.

I need the top 10 placed results returned per team. So it can be any placement just the best 10 out of all their results. Then sum up the points for those places.

This seems to be only returning if they placed higher than 10th place.

Thanks again.

10. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by mpryan73
This seems to be only returning if they placed higher than 10th place.
are you sure? or did you just look at the number 10 in my solution and make an assumption?

please give me some real test data to work with -- you needn't provide more than 3 or 4 places per team, we don't have to test with the top 10

however, the stuff you posted earlier is not enough to run a meaningful test

11. Registered User
Join Date
Dec 2010
Posts
8
I just sent you an email via your website. thanks

12. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

it ran in 1.3 seconds (mysql 4.1 on a 5-yr-old windows desktop)

i spotchecked the data and it's working correctly

maybe we should revisit what your original question was?

13. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
in other words, maybe i misunderstood "top 10 placed results"

i was doing it based on the highest 10 values in the Points colulmn

14. Registered User
Join Date
Dec 2010
Posts
8
r937,

Thanks I just emailed some screen shots and a better explination. Thanks for your help.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•