sql - PostgreSQL: Finding the total line total for each product that was sold in the largest order? -

i'm college student taking database course, using postgresql. there 1 question seem's give me trouble.

here 2 tables:

table1-orders

fields-id, order_date, customer_id, credit_card_number, cvv, , order_total

table2-order_lines

fields-id, order_id, product_id, quantity, sell_price, , line_total

here question:

what total line_total each product sold in largest order? (largest order_total). enter 0 if product not in largest order. make sure enter both decimal places.

so far, syntax have:

select product_id,  sum (line_total * (1) * quantity) order_lines group product_id; 

i able output product_id total sum of line_total wondering how total line_total each product sold in largest order.

should find id of largest order based on order_total? use sub query merge the 2 tables final answer? using sell_price field syntax have above?

first need largest order. done taking first record ordered order_total desc:

select  *    orders order         order_total desc limit   1 

then need products in largest order. join order_lines , select distinct product ids:

select  distinct         product_id    (         select  *            orders         order                 order_total desc         limit   1         ) o join    order_lines ol on      ol.order_id = o.id 

finally, need join resultset query, substituting sums zeros if product not in largest order:

select  product_id,         case when lo.product_id null sum_line_total else 0 end    (         select  product_id, sum(line_total) sum_line_total            order_lines         group                 product_id         ) ps left join         (         select  distinct                 product_id            (                 select  *                    orders                 order                         order_total desc                 limit   1                 ) o         join    order_lines ol         on      ol.order_id = o.i         ) lo using   (product_id) 

we further optimize query not calculating sums products not in largest order, enough going.