This query was already developed and produces line by line totals, then is copy/pasted into Excel to get Aggregate Totals. I am sure this syntax can be altered to produce Aggregate Totals - I just do not have the skillset to achieve such.
Right now the syntax produces
Big Boy Records 200 400 0.00 Big Boy Records 400 200 10.00 Big Boy Records 300 100 4.00
And copied into Excel to get the below
Big Boy Records 900 700 14.00
What change needs to be made in this query to get the aggregate totals like above?
SELECT vendor.venID,vendor.venName,(( select coalesce(sum(rl.amountToInvoice),0) from AR r join ARItems rl on r.arID=rl.AR join scottCalvin sc on rl.scottCalvin=sc.asID where rl.AR=AR.arID and sc.arRPT=true )) AS TotalAmountToInvoice,(( select coalesce(sum(ja.actualcost),0) from workDetails ja where ja.workID=AR.workID and ja. vendorID=AR.vendorTran )) AS costamt,(( select coalesce(sum(ja.markup),0) from workDetails ja where ja.workID=AR.workID and ja. vendorID=AR.vendorTran )) AS fullon FROM "public".AR AR LEFT OUTER JOIN "public".vendor vendor ON AR.venID=vendor.venID WHERE ((NOT ( (AR.vendorTran = 'Pending') ))) AND ((NOT ( (AR.vendorTran = 'Basis') ))) AND ((AR.payDay BETWEEN '01/01/2017' AND '02/01/2017' AND vendor.venName = 'Big Boy Records')) ORDER BY vendor.venID DESC