How to display SUM fields from a detailed table in a master table

Question

What is the best approach to display the summery of DETAILED.Fields in its master table?

E.g. I have a master table called 'BILL' with all the bill related data and a detailed table ('BILL_DETAIL') with the bill detailed related data, like NAME, PRICE, TAX, ... Now I want to list all BILLS, without the details, but with the sum of the PRICE and TAX stored in the detail table.

Here is a simplified schema of that tables:

TABLE BILL
----------
- ID
- NAME
- ADDRESS
- ...

TABLE BILL_DETAIL
-----------------
- ID
- BILLID
- PORDUCT_NAME
- PRICE
- TAX
- ...

The retrieved table row should look like this:

BILL.CUSTOMER_NAME, BILL.CUSTOMER_ADDRESS, sum(BILL_DETAIL.PRICE), sum(BILL.DETAIL.TAX), ...

Any sugguestions?

Answer 1

A simple GROUP BY and a LEFT JOIN should get you what you want:

Select
  bill.customer_name,
  bill.customer_address,
  Sum(bill_detail.price),
  Sum(bill_detail.tax)
From bill
Left Join bill_detail On ( bill_detail.billid = bill.id )
Group By bill.id, bill.customer_name, bill.customer_address

Make sure to group by all columns of table bill that you have in the column list of your select.