sql - MySQL query sum and group suggestion needed -

i'm trying while query running, more difficult thought. have following 3 tables:

stores: +--+-------+--------------+-------+---------+---------+----+---------------+---+------+ |id|company|address       |postcod|latitude |longitude|stat|   division    |gro| when | +--+-------+--------------+-------+---------+---------+----+---------------+---+------+ |17|company|site address 3|wf1 5nt|53.666340|-1.487857|open|test division 2|arl|2014-01-31 14:36:04 |18|company|site address 3|wf1 5nt|53.666340|-1.487857|open|test division 2|arl|2014-01-31 14:36:04 |19|company|site address 3|wf1 5nt|53.666340|-1.487857|open|test division 2|arl|2014-01-31 14:36:04 |20|company|site address 3|wf1 5nt|53.666340|-1.487857|open|test division 2|arl|2014-01-31 14:36:04 +--+-------+--------------+-------+---------+---------+----+---------------+---+------+  jobs: +--------+-------------------------------------------+-------+------------+------+--------+ | client | description                               | freq  |       | till |job_id | +--------+-------------------------------------------+-------+------------+------+--------+ |     17 | weekly external , internal window clean |  7    | 2013-10-01 | null |    17 | |     18 | weekly external , internal window clean |  7    | 2013-10-01 | null |    18 | |     19 | weekly external , internal window clean |  7    | 2013-10-01 | null |    19 | |     20 | weekly external , internal window clean |  7    | 2013-10-01 | null |    20 | |     17 | 4 weekly fascia , upper floor windows   | 28    | 2013-10-01 | null |   645 | |     18 | 4 weekly fascia , upper floor windows   | 28    | 2013-10-01 | null |   646 | |     19 | 4 weekly fascia , upper floor windows   | 28    | 2013-10-01 | null |   647 | |     20 | 4 weekly fascia , upper floor windows   | 28    | 2013-10-01 | null |   648 | +--------+-------------------------------------------+-------+------------+------+--------+   job_hist +------+-------------------+-----------------+---------+------+--------------+-----------------------------------------+-------------------------------+------+-----+-----------------------+------+ |job_id|       last        |       user      | company | link | signedy_by   |       job description                   |          address              | accu |days | possition             |uniqid| +------+-------------------+-----------------+---------+------+--------------+-----------------------------------------+-------------------------------+------+-----+-----------------------+------+ |17    |2013-10-01 09:35:37|arl_operative_013|santander| link |tony moore    |weekly external window clean             | welling 14 br (da16 3pp)   |739.00|10   |branch manager         |132   | |20    |2013-10-02 12:27:51|arl_operative_013|santander| link |alex goodman  |weekly external window clean             | harold hill 69 fr (rm3 8xa)|55.00 |6    |store assistant manager|268   | |19    |2013-10-03 09:14:19|arl_operative_013|santander| link |darren pickett|weekly external window clean             | woolwich 41 ps (se18 6jd)  |50.00 |5    |other                  |332   | |18    |2013-10-03 09:54:49|arl_operative_013|santander| link |james lawrence|weekly external window clean             | eltham 73 ehs (se9 1uw)    |49.00 |7    |other                  |346   | |17    |2013-10-08 09:05:16|arl_operative_013|santander| link |tony moore    |weekly external , internal window clean| welling 14 br (da16 3pp)   |67.00 |6    |branch manager         |697   | +------+-------------------+-----------------+---------+------+--------------+-----------------------------------------+-------------------------------+------+-----+-----------------------+------+ 

and need write query performance calculation based on divisions. goal to: 1) calculate how many cleans scheduled in given time frame, (simple version) take start date , end date, calculate numbers of day , divide stores.freq

2) how many of scheduled cleans done on time in period. take data job_hist (where job_hist.jod_id=jobs.job_id , job_hist.last falls in interval , job_hist.days <= freq

3) percentage of cleans done on time

4) how many cleans done late (same logic in point 2 job_hist> freq)

5) percentage of cleans done late

6) how many missed (scheduled - done on time - done late)

7) percentage of missed

all should grouped division, result should this:

+------------------------------+---------+------+-----------------+---------+--------------------+------+-----------------+ |division                      |scheduled|ontime|ontime_percentage|completed|completed_percentage|missed|missed_percentage +------------------------------+---------+------+-----------------+---------+--------------------+------+-----------------+ |fsdfoihsdfljksdlgjdfsligsgfsfd|16282    |10404 |63.90%           |10825    |66.48%              |5457  |33.52% |test division 2               |259      |129   |49.81%           |133      |51.35%              |126   |48.65% |test division 3               |30       |15    |50.00%           |15       |50.00%              |15    |50.00% +------------------------------+---------+------+-----------------+---------+--------------------+------+-----------------+ 

now i'm not sql queries, have managed put following query:

select `stores`.`division`,  sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`) `scheduled`,  count(`name`)  `ontime`,  concat(round(( count(`name`)/sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`) * 100 ),2),'%') ontime_percentage,  count(`user`) `completed`, concat(round(( count(`user`)/sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`) * 100 ),2),'%') completed_percentage, (sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`)-count(`user`)) `missed`, concat(round(( (sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`)-count(`user`)) /sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`) * 100 ),2),'%') missed_percentage `stores` left join `jobs` on `stores`.`id`=`jobs`.`client` left join (select `user`,`job_id` `job_hist`  `job_hist`.`last`>='$from' , `job_hist`.`last`<='$till') `myquery` on `myquery`.`job_id`=`jobs`.`job_id` left join (select `name`,`job_hist`.`job_id` `job_hist` left join `jobs` on `jobs`.`job_id`=`job_hist`.`job_id` `job_hist`.`last`>='$from' , `job_hist`.`last`<='$till' , `job_hist`.`days`<=`jobs`.`freq`) `myquery2` on `myquery2`.`job_id`=`jobs`.`job_id` `stores`.`owner`='$group' group `division` 

the $till, $from, $group php variables, $till , $from dates in string formats , $group string.

now query runs nicely, (there but), scheduled field summing different values, mean doubling value, it's tripling it.

i have read this still wasn't able head around it. more or less understand i'm of results doubled, still cannot figure out need change.

before suggest join on division between stores.division , job_hist.division not viable, since divisions can change in time, , updated in stores not in job_hist.job_hist should not modified @ time, containing historical data.

thank help.

update

ok after banging head against brick wall 2 hours managed tweak query , till looks i'm getting right data, need run more tests sure before post own answer. anyway here updated query:

select `stores`.`division`,  sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`)   `scheduled`, sum(`ontime`),  concat(round(( sum(`ontime`)/sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`) * 100 ),2),'%') ontime_percentage,  sum(`completed`-`ontime`),  concat(round(( sum(`completed`-`ontime`)/sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`) * 100 ),2),'%') completed_percentage,  (sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`)-sum(`completed`)) `missed`,  concat(round(( (sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`)-sum(`completed`)) /sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`) * 100 ),2),'%') missed_percentage `stores`  left join `jobs` on `stores`.`id`=`jobs`.`client`  left join (select count(`user`) `completed`,`job_id` `job_hist` `job_hist`.`last`>='$from' , `job_hist`.`last`<='$till' group `job_id`) `myquery` on `myquery`.`job_id`=`jobs`.`job_id`  left join (select count(`name`) `ontime`,`job_hist`.`job_id` `job_hist` left join `jobs` on `jobs`.`job_id`=`job_hist`.`job_id` `job_hist`.`last`>='$from' , `job_hist`.`last`<='$till' , `job_hist`.`days`<=`jobs`.`freq` group `job_id`) `myquery2` on `myquery2`.`job_id`=`jobs`.`job_id`  `stores`.`owner`='$group'  group `stores`.`division` 

if can suggest better way highly appreciate that.

thanks again.

ok.

the problem on grouping here query returns data correctly:

select `stores`.`division`,  sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`)   `scheduled`, sum(`ontime`),  concat(round(( sum(`ontime`)/sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`) * 100 ),2),'%') ontime_percentage,  sum(`completed`)-sum(`ontime`),  concat(round(( (sum(`completed`)-sum(`ontime`))/sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`) * 100 ),2),'%') completed_percentage,  (sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`)-sum(`completed`)) `missed`,  concat(round(( (sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`)-sum(`completed`)) /sum(datediff(least(ifnull(`till`,curdate()),'$till'),greatest(`from`,'$from')) div `freq`) * 100 ),2),'%') missed_percentage `stores`  left join `jobs` on `stores`.`id`=`jobs`.`client`  left join (select count(`user`) `completed`,`job_id` `job_hist` `job_hist`.`last`>='$from' , `job_hist`.`last`<='$till' group `job_id`) `myquery` on `myquery`.`job_id`=`jobs`.`job_id`  left join (select count(`name`) `ontime`,`job_hist`.`job_id` `job_hist` left join `jobs` on `jobs`.`job_id`=`job_hist`.`job_id` `job_hist`.`last`>='$from' , `job_hist`.`last`<='$till' , `job_hist`.`days`<=`jobs`.`freq` group `job_id`) `myquery2` on `myquery2`.`job_id`=`jobs`.`job_id`  `stores`.`owner`='$group'  group `stores`.`division` 

if has better solution please post it.

thank you.