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?
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;
Your select clause includes:
bit_farm.name as fname, bit_regional.name as rname
MySQL will accept this by default, but your system must have the default settings to be ANSI compliant.
It depends what you want. If you want a different value for each "farm" and "region", then add:
group by bit_farm.name, bit_regional.name
If you want an overall total, then remove these columns from the select.