Reputation: 335
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.
Upvotes: 1
Views: 3480
Reputation: 2060
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
Upvotes: 2