select dcash.pettycash pettycash, dcash.id dcashid, DATE_FORMAT(dcash.date, '%e %M %Y') dcashdate, dcash.date dcashdate, sum(dcinv.dcinv_turnover_total) grossto, sum(dcinv.covers) covers_total, sum(dcinv.staffpromo_total) staff_promo, sum(dcinv.returns_total) returns_total, sum(dcinv.discount_total) promo_total, sum(dcinv.eft_total) eft_total, sum(dcinv.creditcards_total) creditcard_total, sum(dcinv.accounts_total) accounts_total, sum(dcinv.cheque_total) cheque_total, sum(dcinv.vat_total) vat_total, sum( (dcinv.dcinv_turnover_total - dcinv.vat_total) - (dcinv.staffpromo_total - dcinv.staffpromo_total/dcinv.dcinv_turnover_total*dcinv.vat_total) - (dcinv.discount_total - dcinv.discount_total/dcinv.dcinv_turnover_total*dcinv.vat_total) - (dcinv.returns_total - dcinv.returns_total/dcinv.dcinv_turnover_total*dcinv.vat_total) ) #- sum(dci.s_fee) AS nettoexc_componenet, sum( ( (dcinv.dcinv_turnover_total - dcinv.vat_total) - (dcinv.staffpromo_total - dcinv.staffpromo_total/dcinv.dcinv_turnover_total*dcinv.vat_total) - (dcinv.discount_total - dcinv.discount_total/dcinv.dcinv_turnover_total*dcinv.vat_total) - (dcinv.returns_total - dcinv.returns_total/dcinv.dcinv_turnover_total*dcinv.vat_total) ) ) / sum(dcinv.covers) AS avgnet, sum( dcinv.dcinv_turnover_total - dcinv.staffpromo_total - dcinv.discount_total - dcinv.returns_total - dcinv.eft_total - dcinv.creditcards_total - dcinv.accounts_total - dcinv.cheque_total ) #- dci.s_fee AS cash_component, sum( dcinv.dcinv_turnover_total - dcinv.staffpromo_total - dcinv.discount_total - dcinv.returns_total - dcinv.eft_total - dcinv.creditcards_total - dcinv.accounts_total - dcinv.cheque_total ) #- dci.s_fee + dci.s_fee_retained AS grosscash_component from dcash, dcashitems dci LEFT JOIN dcashinvoices dcinv ON dcinv.dcashitems_id = dci.id and dcinv.shop_id=dci.shop_id and dcinv.f_status<100 where dcash.date >= '2022-11-22' and dcash.date <= '2022-11-22' and dcash.shop_id=1 and dci.shop_id=dcash.shop_id and dcash.id=dci.dcash_id and dcash.f_status<100 and dci.f_status<100 group by dcash.id;