Thursday, March 8, 2012

Aggregate functions in multiple tables

Hi, need help in this statement here. I have three tables here, i.e. Sales, SalesItem, & SalesPmt. I want to display a grid that shows the Total Bill and Total Payment amounts.

My try is like this: SELECT SalesNo, SUM(Price*Qty) AS TotalBill, SUM(Payment) AS TotalPayment FROM ... GROUP BY...

No syntax error or whatever found, but the result of the total amounts is incorrect.

Say the data of the respective table below:

SalesItem

NoQtyPrice115.002212.00343.50

SalesPayment

NoAmount110.0025.00

But the result I get from the above query is:

TotalBillTotalPayment86.0045.00

Total Bill should be 43.00 and Total Payment should be 15.00.

Apparently the problem is due to the fact that I and querying on multiple tables. The correct total payment amount was multiplied by the number of rows of sales items (15.00 x 3), while the correct total bill amount was multiplied by the number of rows of sale payments (43.00 x 2).

So, what is the better way of writing this query?

Use table name or table alias for each table and show the tablr (or alias) in front of column names.

Your query will look like this:

SELECT a.SalesNo, SUM(a.Price*a.Qty) AS TotalBill, SUM(b.Payment) AS TotalPayment FROM SalesItem AS a INNER JOIN SalesPayment AS b ON a.SalesNo= b.SalesNo GROUP BY a.SalesNo

|||

All the column names are unique in these three tables except the foreign key, SalesNo.

There is no difference happening here... :(

I guess I have to add two more columns in the Sales table and programmatically insert/update the TotalBill & TotalPayment just to have the grid with these two information available.

Or should I, the other way round, programatically calculate them when retriving the resultset for the grid?

Which one is the better way in the sense of processing performance? I think there sure is a difference between these two methods if you are retirving thousands of records...

I bet the first method is better, you think?

|||Could you add the table which is missing from your first post and includes all your key columns?|||

Sorry for late reply, please see the following for tables and key columns:

Table - Sales
SalesNo PK

Table - SalesItem
SalesItemNo PK
SalesNo FK
ItemCode
Qty
Price

Table - SalesPayment
SalesPaymentNo PK
SalesNo FK
Amount

|||

You should use sam data along with your table too.

Here is a query to get 43 and 15. no group by.

SELECT SUM(a.Price*a.Qty) AS TotalBill, SUM(b.Amount) AS TotalPayment
FROM SalesItem AS a LEFT JOIN SalesPayment AS b ON
a.SalesNo= b.SalesNo

No comments:

Post a Comment