0

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.

I tried to embed a select with a total and the order by that seems to take a massive amount of time. 

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;

INDEXES :

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, ,
Eishman
  • 125
  • 2
  • 3
  • 9
  • Try changing your GROUP BY to `GROUP BY DATE_FORMAT(c_module_month, '%b %y'), module_name, c_module_type`. MySQL has a horrible misfeature in its GROUP BY setup. – O. Jones Nov 23 '12 at 14:14
  • Also, it's hard to tell what's going on this query because you haven't qualified your field names with table names. Where are your indexes? Also, if your column c_module_month ONLY contains values referring to the first days of months, you can speed this up a lot by getting rid of the DATE_FORMAT call. – O. Jones Nov 23 '12 at 14:16
  • Thanks qualified field names & shown indexes. I will retry the group by suggestion. I did try earlier but couldn't get it to work. – Eishman Nov 26 '12 at 08:15

1 Answers1

0

I found the 'WITH ROLLUP' feature is doing what I need. The next catch is the find a way to sort in this. Working in it, there is an answer to that challenge here : sort the "rollup" in group by

Community
  • 1
  • 1
Eishman
  • 125
  • 2
  • 3
  • 9