Hi there.

I am trying to get the total value to build an invoice (total of all
parts used for a 'job' plus a labour charge.

I am working on the query involving 3 tables.

tblinvoices

invoiceID (primary key)
jobID
inv_terms
date_sent
inv_notes
few other bits

tbljobs (each invoice has only 1 job)

jobID (primary key)
labour_charge
few other bits

tblparts (a job can have many parts - NOTE that all parts are custom
and so cannot be normalised any further) the price field refers to the
price (each) of the item.

partID (primary key)
jobID 
description
Quantity
price

I need a query to produce this

InvoiceID           Total Amount (all parts for the job + labour)

1                       x
2                       x
3                       x
4                       x

So far, i have this

SELECT sum(tblparts.price*tblparts.quantity)+tbljobs.labour_chargeAS
tester, tblinvoices.invoiceid
FROM tblparts, tbljobs, tblinvoices
WHERE tblparts.jobid=tbljobs.id
GROUP BY tblinvoices.invoiceid, tblparts.id, tblparts.pricecharged,
tblparts.quantity, tbljobs.charge, tbljobs.id;

which gives me

InvoiceID           Total Amount (all parts for the job + labour)

1                       £55.00 (wrong, its only couting the first part)
1                       £55.00
1                       £55.00
2                       £105.00 (also wrong)
2                       £105.00

You have probably guessed, invoiceID 1 has 3 parts and invoiceID 2 has 2.

I think i just the the ORDER BY or GROUP BY wrong (to be totaly honest
i dont really understand the difference).

I know this is very simple but my head is battered! First person with
the correct answer will get the chance to help me get $25,000,000 out
of Nigeria - all they have to do is pay the $8,000,000 "transfer fee"
:-)

Many thanks

Change your GROUP BY clause to this:
GROUP BY tblinvoices.invoiceid

The GROUP BY clause controls which values get summed together. By
including only invoiceid in the GROUP BY clause, you tell the query
that you want the sum for each invoice.

The ORDER BY clause controls how the results are sorted. In your case,
you have no ORDER BY, so your results are returned however the
database sees fit. To sort by invoice id, you can use:
ORDER BY tblinvoices.invoiceid

There are very simple explanations and examples of these clauses at W3 Schools.

W3 Schools - GROUP BY
http://www.w3schools.com/sql/sql_groupby.asp

W3 Schools - ORDER BY
http://www.w3schools.com/sql/sql_orderby.asp

In addition, the Help for your database product likely includes an SQL
language reference of some kind.

Search strategy:
SQL "GROUP BY"

Please request clarification if you need anything explained further.
Good luck with your SQL project!

- Hammer

---

Request for Answer Clarification by apcs_uk-ga on 06 Mar 2004 07:34 PST

hmmm, thats what i thought, but i get this

"you tried to execute a query that does not include the specified
expression 'sum([tblparts].[pricecharged]*[tblparts].[quantity])+[tbljobs].[charge]'
as part of an agregate function.

thanks for your help

---

Clarification of Answer by hammer-ga on 06 Mar 2004 08:12 PST

I'm sorry. I only addressed the GROUP BY section becuase I read your
question as being focused on that. Your query actually has several
other problems. Here is a full query. This is written and tested in
Access. It may need minor adjustments depending on which database
product you are using.

SELECT tblInvoices.invoiceID, (SUM( tblParts.quantity *
tblParts.price) + MAX( tblJobs.labour_charge)) AS Tester
FROM (tblInvoices INNER JOIN tblJobs ON tblInvoices.jobID =
tblJobs.jobID) INNER JOIN tblParts ON tblJobs.jobID = tblParts.jobID
GROUP BY  tblInvoices.invoiceID;

Notes:
1. Note that there are now joins for all three tables in your query.
Your original only joined in two of your tables.
2. All fields not included in the GROUP BY are now encompassed by an
aggregate. I used MAX to get the labour charge. Since the labour
charge will be the same for each part on a particular job, either MAX
or MIN can be used to make sure it gets added in only once, rather
than once per Part record.

Give this a try. If you need further clarification, please include
which database you are using.

- Hammer

Works perfectly.

Nigeria?

Are you sure that this is not a scam?