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
GROUP BY
. – mustaccio Apr 18 '17 at 13:49SELECT
list must be present in theGROUP BY
list. – mustaccio Apr 18 '17 at 14:07