Q:

PostgreSQL Total of Count & Sum Summary Line Formatting

I have a Postgres query I am working out where I join two tables.
One table is a 'contract' table that has contract_json JSON attributes stored in a JSONB column which stores the amount.
The other table 'contract_folks' stores all of the individual(s) that are part of the contract and can relate back to a contract by contract_id.
Here is the query I have so far:
SELECT 
contract_folks.name AS "Financier"
,contract.contract_json->>'contractor' as "Contractor
,COUNT(contract.id) as "Count of Contracts by Contractor"
,SUM((contract.contract_json->>'amount')::float8::numeric::money) as "Total Dollars Per Contractor"
FROM contract
JOIN contract_folks on contract.id = contract_folks.document_id
WHERE 1=1
AND contract_folks.folk_type = 1 -- 'contractors' (type 0 is financiers in contract_folks table)
GROUP BY  contract_folks.name, contract.contract_json->>'lead_provider'
ORDER BY contract_folks.name ASC

The results end up looking like this:

I would like for the Financier column to only show the Financier name just once and the rest blank like so with a total (of count and sum) lines for each (not sure if this is possible by modifying my query or if it would be better to use a function?):

If this is a duplicated question then please point me in the right direction and I will be grateful!  Also, thank you for taking a look and any help/suggestions offered are greatly appreciated!

A:

You can use grouping sets to do the subtotals for you.
with roll as (
  SELECT contract_folks.name AS financier,
         contract.contract_json->>'contractor' as contractor,
         COUNT(contract.id) as contract_count,
         SUM((contract.contract_json->>'amount')::float8::numeric::money) 
            as contractor_dollars,
         GROUPING(contract_folks.name, contract.contract_json->>'lead_provider')
            as grp_map
    FROM contract
    JOIN contract_folks on contract.id = contract_folks.document_id
   WHERE 1=1
     AND contract_folks.folk_type = 1 -- 'contractors' (type 0 is financiers in contract_folks table)
GROUP BY ROLLUP(contract_folks.name, contract.contract_json->>'lead_provider')
)
select case 
         when grp_map = 1 then 'TOTAL FOR '||financier,
         when grp_map = 3 then 'GRAND TOTAL'
         when financier = lag(financier) over (order by financier, contractor, grp_num) then null
         else financier
       end as "Financier",
       contractor as "Contractor",
       contract_count as "Count of Contracts by Contractor",
       contract_dollars as "Total Dollars Per Contractor"
  from roll
 order by financier, contractor, grp_num;

If you do not want the GRAND TOTAL line, then exclude it with WHERE grp_num != 3 in the final query.
If you want percentages, join back into the rollup for financier-level dollars and the grand total with something like this:
with roll as (
  SELECT contract_folks.name AS financier,
         contract.contract_json->>'contractor' as contractor,
         COUNT(contract.id) as contract_count,
         SUM((contract.contract_json->>'amount')::float8::numeric::money) 
            as contractor_dollars,
         GROUPING(contract_folks.name, contract.contract_json->>'lead_provider')
            as grp_map
    FROM contract
    JOIN contract_folks on contract.id = contract_folks.document_id
   WHERE 1=1
     AND contract_folks.folk_type = 1 -- 'contractors' (type 0 is financiers in contract_folks table)
GROUP BY ROLLUP(contract_folks.name, contract.contract_json->>'lead_provider')
)
select case 
         when d.grp_map = 1 then 'TOTAL FOR '||d.financier,
         when d.grp_map = 3 then 'GRAND TOTAL'
         when d.financier = lag(d.financier) over (order by d.financier, d.contractor, d.grp_num) then null
         else d.financier
       end as "Financier",
       d.contractor as "Contractor",
       d.contract_count as "Count of Contracts by Contractor",
       d.contract_dollars as "Total Dollars Per Contractor",
       case 
         when d.grp_num = 0 then 100.0 * d.contract_dollars / f.contract_dollars 
         else null
       end as "Share within Financier",
       case 
         when d.grp_num != 3 then 100.0 * d.contract_dollars / g.contract_dollars
         else null
       end as "Share of Grand Total"
  from roll d
  left join roll f on f.financier = d.financier and f.grp_num = 1
  left join roll g on g.grp_num = 3
 order by d.financier, d.contractor, d.grp_num;

