question

asarour avatar image
asarour asked

Two levels aggregation

Hi, I have two tables 'Invoices' and 'Invoice_line'. The 'invoices' table has three fields 'cus_ID', 'inv_ID' , and 'inv_Date'. The 'invoice_Line' table has five fields 'Line_id', 'inv_ID' , 'item_ID' , 'item_Qty' , 'item_price'. I need to write a query to get all customers, number_of_invoices, and customers' total_purchase_amount. any idea without using subquery?

customer, Number of invoices, total purchase amount

101, 5, 1000

102, 20, 2000

aggregation
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
anthony.green avatar image
anthony.green answered

You don't need to use a subquery for this, you would just join the two tables together and then use the correct aggregate functions to achieve the correct output.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.