Page 1 of 1
[SOLVED] Merge 2 sql statements and do calculations
Posted: Wed Apr 25, 2007 3:22 am
by facets
Hi All,
Is there any way to merge these 2 sql statements and do calculations (difference)?
ie, invoices - returns?
Or is the best wat to pull the data into php array and do the calculations from there?
Any ideas would be appreciated.
Code: Select all
select client_id, date, sum(total) as Total from invoices where date between '2007-01-01' AND '2007-03-31' group by client_id order by client_id;
select client_id, date, sum(total) as Total from returns_table where date between '2007-01-01' AND '2007-03-31'group by client_id order by client_id;
tia, Will./
Re: Merge 2 sql statements and do calculations
Posted: Wed Apr 25, 2007 3:57 am
by timvw
SELECT SUM(returns_table.total) - SUM(invoices.total) AS result FROM invoices, returns_table WHERE ....
Posted: Wed Apr 25, 2007 5:36 am
by facets
thanks Tim.
Wow. So many conflicting results.
I know my answer is $8204.35 as the first 2 sql examples show. But when I try the calculation I get an answer that's cosmic to say the least.
Any pointers?
Code: Select all
SELECT SUM(invoices.total) AS result FROM invoices WHERE invoices.client_id = 31;
+----------+
| result |
+----------+
| 11471.09 |
+----------+
1 row in set (0.35 sec)
Code: Select all
select SUM(returns_table.total) AS result FROM returns_table WHERE returns_table.client_id = 31;
+----------+
| result |
+----------+
| -3266.74 |
+----------+
1 row in set (0.17 sec)
Code: Select all
SELECT SUM(invoices.total) - SUM(returns_table.total) AS result FROM invoices, returns_table WHERE invoices.client_id AND returns_table.client_id = 31;
+-----------------+
| result |
+-----------------+
| 66276797.759981 |
+-----------------+
And this sql gives all results but is way out too.
Code: Select all
SELECT SUM(invoices.total) - SUM(returns_table.total) AS result FROM invoices, returns_table WHERE invoices.client_id=returns_table.client_id group by invoices.client_id;
Posted: Wed Apr 25, 2007 6:30 am
by timvw
It's probably something like:
Code: Select all
SELECT
SUM(invoices.total) - SUM(returns_table.total) AS result, invoices.date, invoices.client_id
FROM
invoices, returns_table
WHERE
invoices.client_id = returns_table.client_id
AND invoices.date = returns_table.date
AND invoices.date BETWEN '....' AND '...'
GROUP BY
invoices.date, invoices.client_id
Posted: Fri Apr 27, 2007 6:21 am
by printf
timvw wrote:It's probably something like:
Code: Select all
SELECT
SUM(invoices.total) - SUM(returns_table.total) AS result, invoices.date, invoices.client_id
FROM
invoices, returns_table
WHERE
invoices.client_id = returns_table.client_id
AND invoices.date = returns_table.date
AND invoices.date BETWEN '....' AND '...'
GROUP BY
invoices.date, invoices.client_id
That's not going to work, because the JOIN includes all rows everytime a new set of client_id(s) is matched. So you end up with (total matching rows X total sum for all matching rows)
Say for example, table invoices has 3 rows that fall in the date range being questioned...
// table invoices (total)
Code: Select all
row 1 = 10.00
row 2 = 12.00
row 3 = 11.00
Your SUM(invoices.total) would return (99.00) for that client_id. There is no way to JOIN the (2) queries unless column (total) was set to a true or false (IE: 0 or 1) type column, then you could do the JOIN. Seeing the column total is not like that, you need to do a sub-query and LEFT OUTER JOIN on the sub query!
So the optimized query would look like this... (change the dates [2007-01-01, 2007-03-31] to the range your wanting to query for)
Code: Select all
SELECT a.client_id, ( SUM(a.total) - b.total ) AS total_difference FROM invoices AS a LEFT OUTER JOIN ( SELECT client_id, SUM(total) AS total FROM returns_table WHERE `date` BETWEEN '2007-01-01' AND '2007-03-31' GROUP BY client_id ORDER BY client_id ) AS b ON ( a.client_id = b.client_id AND a.date BETWEEN '2007-01-01' AND '2007-03-31' ) GROUP BY a.client_id ORDER BY a.client_id;
Posted: Fri Apr 27, 2007 8:49 pm
by facets
printf, that's exactly what was happening.
I'm getting a lot of NULL with the code you posted, I think it's related to the date ranges. If I remove the date ranges for invoices (only) I get an expected result.
Is there another way to add a WHERE clause to the invoice select? I've tried adding to the 'FROM invoices AS a' line
Code: Select all
FROM invoices WHERE `date` BETWEEN '2007-01-01' AND '2007-03-31' AS a
But throws an error along the lines of
'AS a LEFT OUTER JOIN ( SELECT client_id, SUM(total) AS total FROM returns_tabl' at line 2
Here's the edited /mostly working/ code :
Code: Select all
SELECT a.client_id, ( SUM(a.total) - (- b.total) ) AS total_difference
FROM invoices AS a
LEFT OUTER JOIN ( SELECT client_id, SUM(total) AS total
FROM returns_table WHERE `date` BETWEEN '2007-01-01' AND '2007-03-31'
GROUP BY client_id
ORDER BY client_id ) AS b
ON ( a.client_id = b.client_id)
GROUP BY a.client_id ORDER BY a.client_id;
Any ideas on this last piece of the puzzle?
tia, Will./
Posted: Fri Apr 27, 2007 10:27 pm
by facets
Thanks to everyone. I worked it out..
Code: Select all
SELECT a.client_id, ( SUM(a.total) - (- b.total) ) AS total_difference
FROM invoices AS a
LEFT OUTER JOIN ( SELECT client_id, SUM(total) AS total
FROM returns_table WHERE `date` BETWEEN '2007-01-01' AND '2007-03-31'
GROUP BY client_id
ORDER BY client_id ) AS b
ON ( a.client_id = b.client_id)
WHERE `date` BETWEEN '2007-01-01' AND '2007-03-31'
GROUP BY a.client_id
ORDER BY a.client_id