{"id":34,"date":"2006-08-01T15:48:37","date_gmt":"2006-08-01T15:48:37","guid":{"rendered":"http:\/\/thenoyes.com\/littlenoise\/?p=34"},"modified":"2006-09-04T20:52:36","modified_gmt":"2006-09-04T20:52:36","slug":"sum-top-n-of-a-group","status":"publish","type":"post","link":"https:\/\/thenoyes.com\/littlenoise\/?p=34","title":{"rendered":"Sum top N of a group"},"content":{"rendered":"<p>Trying to sum the top 5 entries in each group.<\/p>\n<pre>SELECT\r\n    theId,\r\n    SUM(theData) FROM (\r\n        (SELECT\r\n            @count := 0, \r\n            @oldId := 0 AS theId, \r\n            0 AS theData) \r\n        UNION \r\n        (SELECT \r\n            @count := if(@oldId = id, @count+1, 0), \r\n            @oldId := id, \r\n            if(@count &lt; 5, a, 0) \r\n        FROM \r\n            (SELECT * FROM theTable ORDER BY id, a DESC) AS theTable \r\n        ) \r\n    ) AS theView \r\nWHERE theId != 0 \r\nGROUP BY theId;<\/pre>\n<p>Table population:<\/p>\n<pre>INSERT INTO `thetable` VALUES \r\n    (1,1), (1,2), (1,3), (1,4),\r\n    (1,5), (1,6), (1,7), (2,1),\r\n    (2,2), (2,3), (2,4), (2,5);<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Trying to sum the top 5 entries in each group. SELECT theId, SUM(theData) FROM ( (SELECT @count := 0, @oldId := 0 AS theId, 0 AS theData) UNION (SELECT @count := if(@oldId = id, @count+1, 0), @oldId := id, if(@count &lt; 5, a, 0) FROM (SELECT * FROM theTable ORDER BY id, a DESC) AS [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[8],"tags":[],"class_list":["post-34","post","type-post","status-publish","format-standard","hentry","category-mysql-faq"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-y","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/34","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=34"}],"version-history":[{"count":0,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/34\/revisions"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=34"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=34"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=34"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}