Results 1 to 14 of 14
  1. #1
    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    8

    No records

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

  4. #4
    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. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would you please do a SHOW CREATE TABLE so i can see your indexes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    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', ''

    Thanks for your help.

  7. #7
    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. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mpryan73 View Post
    Or this
    yeah, that

    try this --
    Code:
    ALTER TABLE Event_Results
    ADD INDEX (Catergory,EventYear,TeamName)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2010
    Posts
    8
    That made a difference.
    I appreciate your help.


    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. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mpryan73 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i installed andloaded your table, and tested my query

    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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