Show CUBE PostgreSQL results with all possible dimensions

Question

I use the following query to get cube results for pivot table:

SELECT
    client,
    shop,
    SUM (sales)
FROM
    sales
GROUP BY
    CUBE (client, shop)
ORDER BY
    client,
    shop;

And get an output:

client     shop       sales
null       null        1000
null       Shop A       400
null       Shop B       600
Client A   null         800
Client A   Shop A       200
Client A   Shop B       600
Client B   null         200
Client B   Shop A       200

Please note that Client B sales only to Shop A.

But I need an output (added a last line) where output shows all possible intersections in dimensions (client, shop). How to change my query?

client     shop       sales
null       null        1000
null       Shop A       400
null       Shop B       600
Client A   null         800
Client A   Shop A       200
Client A   Shop B       600
Client B   null         200
Client B   Shop A       200
Client B   Shop B      null

Answer 1

You need to query the cartesian product of client and shop left-joined with the table:

select 
    client, 
    shop, 
    sum(sales)
from (
    select distinct client 
    from sales
    ) c
cross join (
    select distinct shop 
    from sales
    ) s
left join 
    sales t using (client, shop)
group by 
    cube(client, shop)
order by 
    client, shop;

  client  |  shop  | sum  
----------+--------+------
 Client A | Shop A |  200
 Client A | Shop B |  600
 Client A |        |  800
 Client B | Shop A |  200
 Client B | Shop B |     
 Client B |        |  200
          | Shop A |  400
          | Shop B |  600
          |        | 1000
(9 rows)