[SOLVED] Merge 2 sql statements and do calculations

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

[SOLVED] Merge 2 sql statements and do calculations

Post 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./
Last edited by facets on Sat Apr 28, 2007 8:23 am, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Merge 2 sql statements and do calculations

Post by timvw »

SELECT SUM(returns_table.total) - SUM(invoices.total) AS result FROM invoices, returns_table WHERE ....
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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;
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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;
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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./
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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
Post Reply