This select statement will give you the job name and difference between the
actual amounts and quote amounts fo all jobs that have both quotes and
actuals:
SELECT job.name,sum(actual.actual_amount)-sum(quote.quote_amount)
FROM quote, job, actual
WHERE (job.job_id=actual.job_id) and (quote.quote_id=job.job_id)
GROUP BY job.name
However your requirement that there may be 0 or more actuals and quotes per
job means you need to use outer joins. Unfortunately you can't do 2 outer
join in a select statement. If you can assume that every job has quotes then
you can get your answer like this:
SELECT job.name, sum(actual.actual_amount)-sum(quote.quote_amount)
FROM quote, {oj job LEFT OUTER JOIN actual ON (job.job_id=actual.job_id)}
WHERE ((quote.quote_id=job.job_id))
GROUP BY job.name
Hope this helps.
Dave Shelley
-----Original Message-----
From: Beau Hartshorne [mailto:[EMAIL PROTECTED]
Sent: Friday, May 30, 2003 4:57 PM
To: [EMAIL PROTECTED]
Subject: one query, two tables, two aggregate functions
Hi,
I would like to use one query to compare aggregate values from columns
in two separate tables. I am running mysql 3.23.55 (max). Here is a
simplified table structure:
job
- job_id
- name
quote
- quote_id
- job_id
- quote_amount
actual
- actual_id
- job_id
- actual_amount
I'm trying to figure out the difference between the actual value and the
quoted value. The actual and quote tables may have 0->infinity records.
To find the difference for only one job, I could write two queries like
this:
SELECT sum(quote_amount) AS quote_amount
FROM quote
GROUP BY job_id
WHERE job_id = 1
SELECT sum(actual_amount) AS actual_amount
FROM quote
GROUP BY job_id
WHERE job_id = 1
And then find the difference:
quote_amount - actual_amount
But I'd like to do this for, say, 100 jobs at a time. Can anyone at
least point me in the right direction? Should I be looking at temporary
tables? Should I think about my table structure?
Thanks!!
Beau
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
