Stewed over this for weeks..

[phptek]

Hi folks:

WAMP Setup using XP Prof and MySQL 4.0.17-nt

I have a MySQL query which I have been stewing over for weeks I have a users table (id, name etc), a companies table (id, name etc) an invoices table (id, pay-rate,hours etc) and an expenses table (id, expense-amount etc)

I have narrowed down the problem to my use of the SQL SUM() function. I guess I must be getting confused with what it does. I'm trying to get a total to date for each users' submitted invoices and expenses:

SELECT 
company_name AS CompName, 
company_vatreg	AS CompVatReg, 
user_name AS UserName,
SUM(expense_amount) AS ExpTotalToDate,
SUM(invoice_payrate * invoice_hours) AS InvTotalToDate
FROM 
users, 
companies, 
invoices, 
expenses 
WHERE 
user_id = invoice_user_id 
AND user_id = expense_user_id 
AND user_company_id = company_id
GROUP BY 
user_id 
ORDER BY 
company_name

The problem lies with the line: SUM(invoice_payrate * invoice_hours) AS InvTotalToDate.

If there is just one row in the invoices table, where invoice_payrate = 25.00 and invoice_hours = 50, then 17500.00 gets returned instead of 1250.00 (50 x 25.00)

If I insert an identical additional record, the result should be 2500.00 (two records both with the same total: (50 * 25.00) * 2 = 2500, instead, 140000.00 is returned...

All fields are FLOAT(6,2) where necessary and INT fields elsewhere.

Thanks for any pointers you may be able to give

[r937]

sounds like you are getting a cross join effect

i cannot help until i understand the one-to-many relationships involved between all tables

what/where are the foreign keys?

[phptek]

r937 - thanks for your reply:

Relations are as follows:

* user_id 1:N invoice_user_id (1 user can submit many invoices)
* user_id 1:N expense_user_id (1 user can submit many expenses)
* company_id 1:N user_company_id (1 company is associated with many users)

I want to be able to display the amounts to date, claimed in expenses and submitted in invoices for each user, while also showing company data for each users company.

Like I said I'm testing this on WAMP (MySQL.4.0.17-nt) but will be implementing it on LAMP (MySQL 4.0.22), just in case this makes any difference

Thanks again for any pointers

[r937]

because you are not yet on 4.1 which supports subqueries and derived tables, you will need to break this query apart into multiple queries that obtain separate totals, and then use a final query to assemble the totals

the problem is that user-invoices and user-expenses are each one-to-many and this is the source of your cross join effect

[phptek]

OK - I thought I might need to do something like that - but I always at least try to get all I can out of one query!

Out of interest what is a derived table? Is it something like "constructing" a table from field values from within a query and then querying that with a subquery or something?

Thanks very much all the same

[r937]

here is your solution using subqueries --

select company_name        as CompName
     , company_vatreg	   as CompVatReg
     , user_name           as UserName
     , ( select sum(expense_amount)
           from expenses
          where expense_user_id 
              = user_id )  as ExpTotalToDate
     , ( select sum(invoice_payrate 
                * invoice_hours)
           from invoices
          where invoice_user_id
              = user_id )  as InvTotalToDate
  from users
inner
  join companies
    on user_company_id 
     = company_id
order 
    by company_name

and here it is using derived tables --

select company_name        as CompName
     , company_vatreg	   as CompVatReg
     , user_name           as UserName
     , dt1.ExpTotalToDate
     , dt2.InvTotalToDate
  from users
inner
  join companies
    on user_company_id 
     = company_id
left outer
  join (
       select user_id
            , sum(expense_amount) as ExpTotalToDate
           from expenses
        group
            by user_id
       ) as dt1
    on expense_user_id
     = dt1.user_id      
left outer
  join (
       select user_id
            , sum(invoice_payrate 
                * invoice_hours) as InvTotalToDate
           from invoices
        group
            by user_id
       ) as dt2
    on invoice_user_id
     = dt2.user_id   
order 
    by company_name