MySQl错误：＃1140 - 如果没有GROUP BY子句，混合GROUP列（MIN（），MAX（），COUNT（），...）没有GROUP列是非法的

Question

I have a query to calculate sum of each column from a table selecting sum and column values.

    $query = "select sum(salleryallowance), 
sum(entertainmentexp),
sum(depreciation), 
sum(electricity), 
sum(securitygard),
sum(machinaryrepaire), 
sum(totalrepairing),
sum(othermaintanaice),
sum(postal_charge),
sum(officeexp), 
sum(stationary),
sum(rent_lease_thresher), 
sum(rent_tractor), 
sum(traivlingallowance),
sum(transportaion_cost), 
sum(bank_commition), 
sum(total_exp),
sum(interest_earned), 
bit_farm.name as fname, 
bit_regional.name as rname 
from bit_income_expenditure 
inner join bit_farm on bit_income_expenditure.farm_id = bit_farm.id 
inner join bit_regional on bit_income_expenditure.region_id = bit_regional.id
";
$fetch = mysql_query($query);

$row = mysql_fetch_array($fetch);

// and print my output

echo $row[0]; //and so on....

When I execute this code, it shows following error:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

How can I solve this error?

Answer 1

If you use any function like sum(),avg(), count()...then by default it will sum/count/avg of all data but if you use any other column based on which you want to categorized your sum/count/avg then you must mention group by after where clause, so your query should be-

select sum(salleryallowance), 
sum(entertainmentexp),
sum(depreciation), 
sum(electricity), 
sum(securitygard),
sum(machinaryrepaire), 
sum(totalrepairing),
sum(othermaintanaice),
sum(postal_charge),
sum(officeexp), 
sum(stationary),
sum(rent_lease_thresher), 
sum(rent_tractor), 
sum(traivlingallowance),
sum(transportaion_cost), 
sum(bank_commition), 
sum(total_exp),
sum(interest_earned), 
bit_farm.name as fname, 
bit_regional.name as rname 
from bit_income_expenditure 
inner join bit_farm on bit_income_expenditure.farm_id = bit_farm.id 
inner join bit_regional on bit_income_expenditure.region_id = bit_regional.id
group by bit_farm.name,bit_regional.name;