如何添加聚合函数来显示报表的总价和数量

Question

这是我迄今为止的代码

SELECT
customer.customer_id AS "ID",
order_item.quantity AS "Quantity",
TO_CHAR (tree.price, 'FML999.99') AS "Total Price"
FROM 
customer
INNER JOIN
order_form ON order_form.customer_id = customer.customer_id
INNER JOIN
order_item ON order_item.order_id = order_form.order_id
INNER JOIN
batch ON batch.tree_id = order_item.tree_id
INNER JOIN
tree ON tree.id = batch.tree_id
WHERE order_item.order_id = '00000072'

输出：

ID        Quantity  Total Price       
------    ---------- -----------------
C93          2       £56.             
C93          1      £27.4            
C93          1       £47.             

我该如何将其显示为

所需输出：

ID       Quantity Total Pice       
------ ---------- -----------------
C93 ,         4,     £130.4 

将非常感谢所有回复

谢谢

Answer 1

聚合；group by子句必须包含未聚合的列。

SELECT customer.customer_id AS "ID",
SUM (order_item.quantity) AS "Quantity",
TO_CHAR (SUM (tree.price), 'FML999.99') AS "Total Price"
FROM customer
INNER JOIN order_form ON order_form.customer_id = customer.customer_id
INNER JOIN order_item ON order_item.order_id = order_form.order_id
INNER JOIN batch ON batch.tree_id = order_item.tree_id
INNER JOIN tree ON tree.id = batch.tree_id
WHERE order_item.order_id = '00000072'
GROUP BY customer.customer_id