StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGroup by Highest Total Month
text
Body
copied!<p>I am looking to order the MySQL statement below by the Module name (as it currently is) BUT only include the Modules that have the top 50 as far as the COUNT for all module_types included in the date range is concerned. Any ideas for how I would go about doing that? ie. If the total for January, Feb, March of all the 'counts' of a module is higher than same for another module then all three months for that module would go above the other records.</p> <p>I tried to embed a select with a total and the order by that seems to take a massive amount of time. </p> <pre><code>SELECT DATE_FORMAT(tbl_client_modules.c_module_month, '%b %y') AS MONTH, tbl_modules.module_name, count(tbl_client_modules.c_module_type), tbl_client_modules.c_module_type AS MOD_TYPE FROM tbl_client_details LEFT OUTER JOIN tbl_client_status ON tbl_client_details.cd_status = tbl_client_status.cl_status_id LEFT OUTER JOIN tbl_client_modules ON tbl_client_details.cd_ZBI_no = tbl_client_modules.cl_module_zbi AND tbl_client_details.cd_UCN = tbl_client_modules.c_module_UCN AND tbl_client_details.cd_co_code = tbl_client_modules.c_module_co_code LEFT OUTER JOIN tbl_modules ON tbl_client_modules.c_module_type = tbl_modules.module_id WHERE tbl_client_details.cd_removed_date is null AND TRIM(tbl_client_details.cd_client_name) <> '' AND (tbl_client_details.cd_region = 'WC') AND (tbl_client_modules.c_module_month >= '2012-07-01' AND tbl_client_modules.c_module_month <= '2012-10-30') AND tbl_client_modules.c_module_vol > 0 GROUP BY tbl_client_modules.c_module_month, tbl_client_modules.c_module_type ORDER BY tbl_modules.module_name; </code></pre> <p>INDEXES :</p> <pre><code>tbl_client_details, 0, PRIMARY, 1, cd_id, A, , , , , BTREE, , tbl_client_details, 0, PRIMARY, 2, cd_ucn, A, , , , , BTREE, , tbl_client_details, 0, PRIMARY, 3, cd_ZBI_no, A, 55351, , , , BTREE, , tbl_client_details, 1, cd_ucn, 1, cd_ucn, A, 55351, , , , BTREE, , tbl_client_details, 1, cd_opm, 1, cd_OPM, A, 321, , , YES, BTREE, , tbl_client_details, 1, cd_zbi_no, 1, cd_ZBI_no, A, 55351, , , , BTREE, , tbl_client_details, 1, cd_cpe_rm_code, 1, cd_cpe_rm_code, A, 1729, , , YES, BTREE, , tbl_client_details, 1, cd_sic_code, 1, cd_sic_code, A, 802, , , YES, BTREE, , tbl_client_details, 1, cd_enrol_date, 1, cd_enrol_date, A, 13837, , , YES, BTREE, , tbl_client_details, 1, cd_co_code, 1, cd_co_code, A, 8, , , YES, BTREE, , tbl_client_details, 1, cd_client_name, 1, cd_client_name, A, 55351, , , YES, BTREE, , tbl_client_details, 1, cd_segment, 1, cd_segment, A, 36, , , , BTREE, , tbl_client_details, 1, cd_region, 1, cd_region, A, 18, , , YES, BTREE, , tbl_client_details, 1, cd_status, 1, cd_status, A, 295, , , YES, BTREE, , tbl_client_status, 0, PRIMARY, 1, cl_status_id, A, 32, , , , BTREE, , tbl_client_status, 1, cl_status_id, 1, cl_status_id, A, 32, , , , BTREE, , tbl_client_modules, 0, PRIMARY, 1, cl_module_id, A, 12135739, , , , BTREE, , tbl_client_modules, 1, cl_module_zbi, 1, cl_module_zbi, A, 53697, , , , BTREE, , tbl_client_modules, 1, c_module_type, 1, c_module_type, A, 104, , , , BTREE, , tbl_client_modules, 1, c_module_month, 1, c_module_month, A, 27, , , YES, BTREE, , tbl_client_modules, 1, c_module_ucn, 1, c_module_ucn, A, 63872, , , YES, BTREE, , tbl_client_modules, 1, c_module_co_code, 1, c_module_co_code, A, 9, , , YES, BTREE, , tbl_client_modules, 1, c_module_vol, 1, c_module_vol, A, 32020, , , YES, BTREE, , tbl_modules, 0, PRIMARY, 1, module_id, A, 109, , , , BTREE, , tbl_modules, 1, module_id, 1, module_id, A, 109, , , , BTREE, , tbl_modules, 1, module_tab, 1, module_tab, A, 9, , , YES, BTREE, , </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload