Problem with SUM() function

frontporch

I'm having a problem with the SUM() function that I just can't figure out.

I've got 2 tables (this is a very simplified version): ServiceMemo and ServiceMemoWork. A ServiceMemo can have multiple ServiceMemoWork records. I need to do a SUM on a column on the ServiceMemo table, but I also need to join to the ServiceMemoWork table for other data. If there are two rows on the ServiceMemoWork table, the SUM function is adding them twice. Is there a way to get around this?

Here is my code:

select sm.id, SUM(sm.memoTotalVPC) AS SumTotalVPC from ServiceMemo sm, ServiceMemoWork smw WHERE sm.id=18486 AND smw.serviceMemoID = sm.id GROUP BY sm.id

Roger_Ramjet

No of course not. When you join 1:M you will always get the data on the 1-side repeated for every row on the M-side. Either you do the SUM() in a sub-query with no join, or you use 2 queries.

You should only ever be doing group by aggregates on the many-table when using a join.

HP400

Maybe...

SELECT sm.id SUM(sm.memoTotalVPC) AS SumTotalVPC
FROM ServiceMemo sm
JOIN ServiceMemoWork smw ON sm.id = smw.serviceMemoID
WHERE sm.id = 18486
GROUP BY sm.id

Roger_Ramjet

No, still not going to work PH. Join returns multiple rows so values in servicememo get repeated and you get the wrong sum.


$sql = "SELECT sm.id, sm.tot, smw.* FROM 
   (SELECT id, sum(memoTotalVPC) as tot FROM ServiceMemo WHERE id=18486  GROUP BY id, memoTotalVPC) as sm 
   INNER JOIN ServiceMemoWork smw ON sm.id = smw.serviceMemoID";

But even as I write that I wonder what the SUM is doing, cos you've only got 1 row in servicememo with the id=18486 ?? Whole Group By thing is unneccessary and the sum is just going to be the same as the column value cos ther is only the one. My guess is that you've not posted the code you are using, for whatever reason.

I'll leave this query as-is though as an exemplar of how you would do this with a sub-query - if you needed one. As it is this query makes no sense but the principle is there.

I can never understand why people post fake code, makes it really difficult to help them. I dunno what they are trying to hide?It's like they think someone is going to steal their 'brilliant' ideas.[/I] :bemused:

HP400

Trying to think if the DISTINCT keyword could be put in to avoid the subquery...

frontporch

No, I'm not worried about someone stealing my code. There just seemed to be a lot of extra stuff in there that might confuse things.

I've got a lookup screen that accepts filter criteria for a report. The report groups by employee and service date. I tried using a subquery as you suggested, but I'm not sure of the syntax as I need to join to the ServiceMemoWork table only to find out if there is a 'No Service' record. If there is, then I don't want anything returned.

Here is my whole sql statement:

$sql = "SELECT DISTINCT sm.employeeID, DATE_FORMAT(sm.completionDate, '%Y-%m-%d') AS serviceDateFmt, SUM(sm.memoTotalVPC) AS SumTotalVPC 
FROM ServiceMemo sm, ServiceMemoWork smw where sm.employeeID = " . $myrow["employeeID"] . " AND sm.completionDate >= '" . $myrow["serviceDateFmt"] . " 00:00:00' AND sm.completionDate <= '" . $myrow["serviceDateFmt"] . " 23:59:59' 
AND smw.serviceMemoID = sm.id  AND sm.id NOT IN (SELECT serviceMemoID FROM ServiceMemoWork WHERE reason='No Service' OR reason='No Service No VPC') 
GROUP BY sm.employeeID, serviceDateFmt";

frontporch

I'm sure that a sub-query will work for this. I'm just having a hard time figuring out the syntax to use. After 12 hours on this problem, I'm ready to do just about anything! Maybe an array or temp table that will hold an indicator of whether this is a 'No Service' memo, then a subsequent join to get the SUM() value??

Roger_Ramjet

Go with the work table approach. I use them all the time in access even though it has named views you can use in queries, which mysql does not. For complex aggregate queries with sub-queries or multiple joins, using a work table can speed up execution no end, as well as making it easier to write the queries, debug them, and document the logic.