SQL get total amount of Column with group by?-CodePudding

Below you can see my SQL query

select c.email_address,o.order_id as "Number of Orders",
((oi.item_price-oi.discount_amount)*oi.quantity) as "Total amount" 
from customers c 
        inner join orders o on c.customer_id=o.customer_id
        inner join order_items oi on o.order_id=oi.order_id

From that, I can get output like below

Email            Orders_id  Amount
[email protected]     1       839.3
[email protected]    2       303.79
[email protected]     3       1208.16
[email protected]     3       253.15
[email protected]    4       1678.6
[email protected]   5       299
[email protected]     6       299
[email protected]     7       489.3
[email protected]     7       559.9
[email protected]     7       489.99
[email protected]     8       679.99
[email protected]   9       489.3

But I want to customize it like below

Email           Number of Orders    Total Amount
[email protected]         3            2300.61
[email protected]        1            303.79
[email protected]        1            1678.6
[email protected]       2            788.3
[email protected]         1            299
[email protected]         3            1539.19
[email protected]         1            679.99

Can anyone help me to do this?

CodePudding user response：

As you said in the question, use GROUP BY and aggregate the number of orders and the total:

select c.email_address,
       COUNT(o.order_id) as "Number of Orders",
       SUM((oi.item_price-oi.discount_amount)*oi.quantity) as "Total amount" 
from   customers c 
       inner join orders o on c.customer_id=o.customer_id
       inner join order_items oi on o.order_id=oi.order_id
GROUP BY c.email_address

CodePudding user response：

You can wrap your query:

select email_address, count("Number of Orders") as number_of_orders, sum("Total Amount") as amount
from (
    select c.email_address,o.order_id as "Number of Orders",
    ((oi.item_price-oi.discount_amount)*oi.quantity) as "Total amount" 
    from customers c 
        inner join orders o on c.customer_id=o.customer_id
        inner join order_items oi on o.order_id=oi.order_id
    )Z
group by email