php - MySQL SUM() giving incorrect total -

i developing php/mysql database. have 2 tables - matters , actions. amongst other fields matter table contains 'matterid' 'fixedfee' , 'fee'. fixed fee y or n , fee can number.

for matter there can number of actions. actions table contains 'actionid' 'matterid' 'advicetime' 'advicefee'. advicetime how long advice goes on (in decimal format) , advicefee number. thus, work out cost of advice matter use sum(advicetime*advicefee).

what wish add of 'fee' values when 'fixedfee'=y , sum of of sum(advicetime*advicefee) values of these matters.

i have tried using:

select         sum(matters.fee) totfixed,         sum(advicetime*advicefee) totbills,     matters     inner join actions         on matters.matterid = actions.matterid     fixedfee = 'y' 

but doesn't work (i think) adding matters.fee every time there action. have tried making

sum(distinct matters.fee) totfixed  

but doesn't work think seems missing out identical fees (and there several matters have same fixed fee).

i new welcome.

but doesn't work (i think) adding matters.fee every time there action. have tried making ...

you're experiencing aggregate fanout issue. happens whenever primary table in select query has fewer rows secondary table joined. join results in duplicate rows. so, when aggregate functions applied, act on rows.

here primary table refers 1 aggregate functions applied. in example,
* sum(matters.fee) >> aggregation on table matters.
* sum(advicetime*advicefee) >> aggregation on table actions
* fixedfee='y' >> condition on table matters

to avoid fanout issue:
* apply aggregates granular table in join.
* unless 2 tables have one-to-one relationship, don't apply aggregate functions on fields both tables.
* obtain aggregates separately through different subqueries , combine result. can done in sql statement, or can export data , it.

query 1:

select sum(fee) totfixed  matters  fixedfee='y' 

query 2:

select sum(actions.advicetime*actions.advicefee) totbills  matters   join actions on matters.matterid = actions.matterid  matters.fixedfee = 'y' 

query 1 & query 2 don't suffer fanout. @ point can export them both , deal result in php. or can combine them in sql:

select query_2.totbills, query_1.totfixed (select sum(fee) totfixed      matters      fixedfee='y') query_1,      (select sum(actions.advicetime*actions.advicefee) totbills      matters       join actions on matters.matterid = actions.matterid      matters.fixedfee = 'y') query_2 

finally, sum not take keyword distinct. distinct available count , group_concat aggregate functions. following piece of invalid sql

sum(distinct matters.fee) totfixed