Creating a summary table in PostgreSQL

Question

I am asked to create an aggregation data from a table using PostgreSQL.

I don't know whether it is possible to do this directly from PostgreSQL since I always write query using SQL Server. To do this, I usually copy the query result to excel and then pivot them.

SELECT 
  date(order_date)
, count(customer_id) as total_customer
, product_category
, sum(quantity) as total_qty
, sum(total_price) as total_price
FROM public."CURRENT_WP_SALES"
WHERE order_status = 'sale'
GROUP BY date(order_date), product_category
ORDER BY date(order_date), product_category asc

The result I get is like this:

+============+================+================+===========+=============+
|    date    | total_customer |    product     | total_qty | total_price |
+============+================+================+===========+=============+
| 2018-12-20 |              2 | frozen food    |         2 |         500 |
+------------+----------------+----------------+-----------+-------------+
| 2018-12-20 |              4 | instant noodle |         5 |         300 |
+------------+----------------+----------------+-----------+-------------+
| 2018-12-20 |              4 | meds           |         1 |          50 |
+------------+----------------+----------------+-----------+-------------+
| 2018-12-20 |              6 | candy          |        10 |         200 |
+------------+----------------+----------------+-----------+-------------+

The expected result is like this:

+============+================+================+===========+=============+
|    date    | total_customer |    product     | total_qty | total_price |
+============+================+================+===========+=============+
|            |                | frozen food    |         2 |             |
+            +                +----------------+-----------+             +
|            |                | instant noodle |         5 |             |
+ 2018-12-20 +       16       +----------------+-----------+     1050    +
|            |                | meds           |         1 |             |
+            +                +----------------+-----------+             +
|            |                | candy          |        10 |             |
+------------+----------------+----------------+-----------+-------------+

If there is any way to do this directly from PostgreSQL, please let me know.

Answer 1

No DBMS would allow exactly what you are asking but there are some close solutions:

SELECT
  date
, SUM(total_customer)
, array_agg(product_category ORDER BY product_category)
, array_agg(total_qty        ORDER BY product_category)
, SUM(total_price)
FROM ( 
        SELECT 
          date(order_date)
        , count(customer_id) as total_customer
        , product_category
        , sum(quantity) as total_qty
        , sum(total_price) as total_price
        FROM public."CURRENT_WP_SALES"
        WHERE order_status = 'sale'
        GROUP BY date(order_date), product_category
) T
GROUP BY date
ORDER BY date