summing columns of two tables into single value 1

[stasJohn]

Hi all

Here's the tables:

stats_activeemployees
| id | lname | fname |

stats_customer
| id | login |

stats_custcredit
| cust_id | amount |

stats_custredeem
| cust_id | amount |

relationships:
stats_activeemployees.id = stats_customer.login
stats_customer.id = stats_custcredit.cust_id
stats_customer.id = stats_custredeem.cust_id

Now I basically want to return a query that looks like
| employee_id | sum(stats_custcredit amount) - sum(stats_custredeem amount) |

I tried the following ...

select ae.id as empid, c.id, sum(cc.amount) - sum(cr.amount) as voucher
from stats_activeemployees as ae, stats_customer as c, stats_custcredit as cc, stats_custredeem as cr
where ae.id = c.login
 and c.id = cc.cust_id
 and c.id = cr.cust_id
group by ae.id

... which semi works. Some totals are correct while others are way off. I noticed that if the employee only has a single row in both stats_custcredit and stats_custredeem, the total comes out accurate. Other than that it is inaccurate.

I'm using mysql4

Thanks in advance.

 

[TonyGroves]

The problem is in your joins. You would need to UNION stats_custcredit and stats_custredeem and treat them as a single table. Since you are using MySQL 4 (I presume you mean 4.0), you can't use a subquery, so you'll have to use a temporary table:
[tt]
CREATE TEMP TABLE t AS
SELECT cust_id,amount cc_amount,0 cr_amount
FROM stats_custcredit
UNION ALL
SELECT cust_id,0,amount
FROM stats_custredeem;

SELECT
ae.id empid, c.id,
SUM(cc_amount) - SUM(cr_amount) voucher
FROM
stats_activeemployees ae
JOIN stats_customer c ON ae.id = c.login
JOIN t ON c.id = cc.cust_id
GROUP BY ae.id,c.id;
[/tt]

 

[stasJohn]

Thanks for the quick reply, but I get the following error

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'TEMP TABLE t AS
SELECT cust_id,amount cc_amount,0 cr_amount

I'm wondering if my host even allows for the creation of temp tables.

 

[TonyGroves]

Maybe it should be TEMPORARY. The MySQL manual shows the correct syntax.

 

[stasJohn]

Aha, yes, TEMPORARY must be used.

There is some funny rounding going on in some cases.

 

[TonyGroves]

No doubt you also spotted my deliberate mistake. The last two lines of the second query should be "JOIN t ON c.id = t.cust_id GROUP BY empid,id".

 

[stasJohn]

Yes I did notice the mistake at the end.

Any idea why the rounding is occurring?

 

[TonyGroves]

No. What sort of rounding?

 

[TonyGroves]

If stats_custredeem.amount is not an integer field, then it would be rounded by that query. To fix it, you could use:
[tt]
CREATE TEMPORARY TABLE t AS
SELECT cust_id,amount cc_amount,0.0 cr_amount
...
[/tt]
This would create the table with the first field the same type as amount, and the second field as a DOUBLE.

 

[stasJohn]

As I was driving home last night it occurred to me that I should try 0.0 instead of 0, which does work. Thamnks again for all the help.