Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-29 : 21:21:39
|
Scott writes "Hi,
Let's say we have a store that sells goods, as well as time(like consultations). We want to show a list of invoices with invoice number, and total dollar amount for each invoice.
We have an invoice table (tbl_invoices) with invoice number, etc as fields... a products ordered table (tbl_products) that has price and quantity ordered as fields, and a services table (tbl_services) that has hours and rate as fields. This is a simplified version of the real thing, of course.
So an invoice will contain products and/or time. SUM(quantity * price) AS product_total will yield the total cost of all products on the invoice, and SUM(hours * rate) time_total will yield the total cost of all time entries on the invoice. Simple.
The hard part (for me at least) is figuring out how to return all that with one query? Can I? The total for the invoice would be time_total + product_total...but I can't seem to come up with a JOIN or GROUP BY that will return what i need. If EVERY invoice had both time entries AND products, I can join the tables and get the proper result, but each invoice can contain time entries, products, or both.
Any advice is appreciated!" |
|
|
|
|
|
Performing calculations inside queries