Get Aggregate Totals

Question

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

Answer 1

Taking it as a given that the query as shown in your question generates the three rows shown, turning the existing query into a subquery would give you the grand total for "Big Boy Records":

SELECT venID, venName
      ,COALESCE(SUM(TotalAmountToInvoice),0) as TotalAmountToInvoice
      ,COALESCE(SUM(costAmt),0) as costAmt
      ,COALESCE(SUM(fullon),0) as fullon
  FROM (
        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
       ) grandTotal
 GROUP BY venID, venName

If this grand total is all you want, you can go back to the original query, GROUP BY venID, venName, and add the SUM() function around each of the other three fields.

As PostgreSQL is not my usual language, I have tried to modify the original query as little as possible. It appears that you want one row per vendor, per AR entry. You might be able to eliminate many of the subqueries (turning them into LEFT JOINs instead), by adding the unique ID from the AR table. You could then use `GROUP BY GROUPING SETS ( (venID, venName, arID), () ) to get the individual lines, with the grand total at the end (or possibly the beginning, not sure how it'd sort). You'd have to include the arID in the SELECT list, I think, but once you've got the results you want, you could find a way to filter that out.