SUM to large ...

saltwater

Hi,

I have a problem concerning SUM with joins. Here is what I want to do:
Every company has one or more contracts, with an ammount of time,
and every contract has some orders, that take an ammount of time
of the the total time of the contract.
Now what I want in my recordset is the taken time by ALL updates of ALL contracts of ALL customers
I tought about doing this with sum, but then I get the total time of a contract multiplyed
by the number of updates for that contract, witch is logical...
maybe something with SUM(if(...))
This are my tables:

company: id(PK) name
data:
id name
1 company1

contract: id(PK) company_id time
data:
id company_id time

1 1 1200
2 1 2500

order: id(PK) contractid time
data:
id contractid time

1 1 50
2 1 60
3 2 100
4 2 10
5 2 150

This is my query:

SELECT
company.name,
COUNT(orders.id) AS ordercount,
SUM(orders.time) AS takentime,
SUM(contract.time) AS totaltime
FROM company
LEFT JOIN contract ON company.id=contract.company_id
LEFT JOIN orders ON contract.id=orders.contractid
GROUP BY company.name

This is my recordset:

name ordercount takentime totaltime

company1 5 370 9900

[deleted]

This is one of those cases where a subquery would be very usefull.

A JOIN just doesn't do it, not even with IF/CASE statements, (You'd have to mess around with internal variables to make sure you get only one count for contract no matter how many orders there are)

Either use subqueries (if your database allows that) or use temporary tables to narrow down your results.

company; total_ordertime; total_contracttime

First insert the companies:

(I did not test these queries, so you may have to fiddle with them)

SELECT companyname, id FROM companies;

then insert the sum of the time-allowed by each contract:

SELECT sum(time) FROM contract, temp_table
WHERE contract.companyid = temp_table.companyid
GROUP BY temp_table.companyid

the inser the total time for all the orders for all the contracts for each company:

SELECT sum(time) FROM orders, contract, temp_table
WHERE contract.companyid = temp_table.companyid
AND orders.contractid = contracts.contractid
GROUP BY temp_table.companyid

Then you should end up with a table that contains the company name, the total time for all contracts, and the total time for all the orders.

saltwater

thx again vincent! 😉