CODEWITHSUNDEEP
mysql
Devisy
Devisy

Reputation: 159

Mysql write query to filter (column1- sum(something)) > 0

i have query:

SELECT cf_1095,@sum:=sum(amount), accountingpaidamount,     
DATE_FORMAT(paymentdate ,'%d/%m/%Y') as tanggal,
DATE_FORMAT(due_date ,'%d/%m/%Y') as tgljatuhtempo, 
vtiger_servicecontracts.subject as kodetb,
vtiger_contactdetails.contactid as memberid,
campaignname as nokk,
vtiger_contactdetails.firstname as namadepan,
vtiger_contactdetails.lastname as  namabelakang,
vtiger_leaddetails.lastname as tipekartu,
cf_1085 as namabank,
cf_1095-sum(amount) as kuranggesek
FROM `vtiger_accounting_payments`, vtiger_accounting,
vtiger_servicecontracts,vtiger_servicecontractscf,     
vtiger_crmentity,vtiger_user2role, vtiger_role, 
vtiger_users,vtiger_leaddetails, vtiger_contactdetails, vtiger_campaign
WHERE
vtiger_crmentity.crmid=vtiger_accounting_payments.idtransaction
and vtiger_accounting.accountingrelated2=vtiger_servicecontracts.servicecontractsid
and vtiger_servicecontractscf.servicecontractsid=vtiger_servicecontracts.servicecontractsid
and accountingtype='Pelunasan Gesek'
and vtiger_accounting_payments.idtransaction=vtiger_accounting.accountingid
and vtiger_crmentity.deleted=0
and vtiger_user2role.userid=vtiger_users.id
and vtiger_crmentity.smcreatorid=vtiger_user2role.userid
and vtiger_user2role.roleid=vtiger_role.roleid
and vtiger_users.user_name='stafroyal'
and cf_1091=leadid
and vtiger_contactdetails.contactid=vtiger_servicecontracts.contactid 
and
vtiger_campaign.campaignid=cf_1155
and date_format(paymentdate,'%Y-%m-%d') < date_format(now(),'%Y-%m-%d') 
group by idtransaction
order by vtiger_servicecontracts.subject

query above successfully display results correctly and column "kuranggesek" will display amount of numbers.

what i am trying to accomplish is I want to add filter 

and  (cf_1095-sum(amount))>0

so zero result on column kuranggesek will be eliminated. But if add those filter, i get error:

Invalid use of group function

can you help me how to fix it?

Thanks

Upvotes: 1

Views: 96

Answers (1)

Jeeva Balan
Jeeva Balan

Reputation: 393

Instead of using aggregate functions in where clause use having clause.

The WHERE clause filters which rows MySQL selects. Then MySQL groups the rows together and aggregates the numbers for your COUNT function.

HAVING is like WHERE, only it happens after the COUNT value has been computed, so it'll work as you expect.

....
group by idtransaction
having kuranggesek>0
order by vtiger_servicecontracts.subject

Upvotes: 1

Related Questions