Need a postgresql query to group and order

Question

I have a customers table as following:

customername, ordername, amount
=============================
bob, book,     20
bob, computer, 40
steve,hat,     15
bill, book,    12
bill, computer, 3
steve, pencil, 10
bill, pen,      2

I want to run a query to get the following result:

customername, ordername, amount
=============================
bob,   computer, 40
bob,   book,     20
bob,   ~total~,  60
steve, hat,      15
steve, pencil, 10
steve, ~total~,25 
bill, book,    12
bill, computer, 3
bill, pen,      2 
bill, ~total~, 17

I want the amount for each customer to be ordered from max to min and a new ordername as "~total~" (must always be the last row for each customer) with a result as sum of all amount for the same customer. So, in above example, bob should be the first since the total=60, steve the second (total=25) and bill the third (total=17).

Answer 1

Use:

SELECT x.customername,
       x.ordername,
       x.amount
  FROM (SELECT a.customername,
               a.ordername,
               a.amount,
               y.rk,
               1 AS sort
          FROM CUSTOMERS a
          JOIN (SELECT c.customername,
                       ROW_NUMBER() OVER (ORDER BY SUM(c.amount) DESC) AS rk
                  FROM CUSTOMERS c
              GROUP BY c.customername) y ON y.customername = a.customername
        UNION ALL
        SELECT b.customername,
               '~total~',
               SUM(b.amount),
               ROW_NUMBER() OVER (ORDER BY SUM(b.amount) DESC) AS rk,              
               2 AS sort
          FROM CUSTOMERS b
      GROUP BY b.customername) x
ORDER BY x.rk, x.customername, x.sort, x.amount DESC

You could look at using GROUP BY ROLLUP, but the ordername value would be NULL so you'd have to post-process it to get that replaced with "~total~"...