PostgreSQL列出一个查询，然后最后一行包含一个sum

Question

我正试图编写一个查询，按用户名显示订单计数，然后我想在最后一行显示所有用户的总数，如下:

我有一个表orders，其中包括username | order# | amount | etc

我想这样显示结果:

batman | 65 superman | 25 robin | 40 Sum: 130

是否可以使用一个查询?目前我使用两个单独的查询:

select username, count(*) as "Number of Orders" from orders group by username;

和

select count(*) as "Number of Orders" from orders;

Answer 1

您可以使用grouping sets:

select username, count(*) as "Number of Orders"
from orders
group by grouping sets ((username), ());

Answer 2

您可以使用UNION ALL

select username, count(*) as "Number of Orders"
from orders
group by username
UNION ALL
select NULL, count(*)
from orders;