Google Answers Logo
View Question
 
Q: sql query to build invoice ( Answered 5 out of 5 stars,   1 Comment )
Question  
Subject: sql query to build invoice
Category: Computers > Programming
Asked by: apcs_uk-ga
List Price: $20.00
Posted: 06 Mar 2004 06:37 PST
Expires: 05 Apr 2004 07:37 PDT
Question ID: 314053
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
Answer  
Subject: Re: sql query to build invoice
Answered By: hammer-ga on 06 Mar 2004 07:11 PST
Rated:5 out of 5 stars
 
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
apcs_uk-ga rated this answer:5 out of 5 stars
Works perfectly.

Comments  
Subject: Re: sql query to build invoice
From: probonopublico-ga on 06 Mar 2004 07:46 PST
 
Nigeria?

Are you sure that this is not a scam?

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy