ExecuteSQL(), JOIN, WHERE, SUM, and GROUP BY

[David Jondreau]

All righty, on a recent thread I realized I may be biased against using JOINS in ExecuteSQL() statements and hence losing a valuable tool. I've decided to rectify this and have a complex result I want, but now I'm stuck.

I have a table TICKETS. This represents a work order performed on a specific date and time with a cost to the customer. The time converts to a relevant shift ( of 1,2, or 3 each 8 hours long, though rarely is there a 3rd shift). There may be 0-50 Tickets run in a Shift. The relevant fields are work_Date, Shift, and Subtotal. Subtotal represents how much a customer is getting charged.

I also have a table TIMES. This represents an employee's work shift. It's got fields like work_Date, Shift, and Total (how much an employee gets paid in his/her shift).

My goal is to create a list of Shifts (actually Date/Shift combos) with the total paid to all employees

 and the totals charged on all tickets for those Date/Shift combos. I could do this with relationships, but I'm hoping to understand ExSQL() better by using it.

I thought I had a decent ExecuteSQL() statement, but the totals aren't correct. They're off by a lot,

My statement is:

SELECT m.work_Date,m.Shift,SUM(m.Total),SUM(k.Subtotal)

FROM Times m LEFT OUTER JOIN Tickets k on m.work_Date = k.Work_Date AND m.Shift = k.Shift

WHERE m.work_Date>='4/1/2013' AND m.work_Date<='4/15/2013' 

GROUP BY M.work_Date,m.shift
ORDER BY m.work_Date,m.shift

and it returns results along the lines of:

2013-04-01,1,2712.88,3661.45
2013-04-01,2,5607.14,5934.28
2013-04-02,1,4476.24,3974.96
2013-04-02,2,14146.54,10219.14

 

I've also tried LEFT JOIN and JOIN instead of LEFT OUT JOIN with the same results.

The format is fine, but the totals aren't right. They should be:

2013-04-01,1,339.11,732.29
2013-04-01,254.87,506.88
2013-04-02,1,248.68,976.69
2013-04-02,2,456.34,993.74
 

Any tips on figuring out why the SUM() on both sides of the JOIN is wrong would be appreciated...

 

DJ

 

[Lee Smith]

Automatic message

This topic has been moved from "The Left Brain" to "FQL - Internal SQL".

[David Jondreau]

Perhaps it's time to change the tag line for this forum:

 

"FQL or FileMaker Query Language - local calls to your database using SQL syntax - using plug-ins"

 

Since plug-ins are no longer required.

[Lee Smith]

Change it.

 

We just created it with the introduction of Execute SQL. I'll pass your comments on to Stephen.

 

Lee

[mr_vodka]

Do you have any tickets that dont have related times records?

[David Jondreau]

Nope.

 

The Times are workers clocking in and out. Tickets represent the work the the workers did. There won't be a Ticket unless there was a worker (with a Times record) to enter it. There may be (rarely) Times with no Tickets (if no work was done).

 

Also, I did a test by adding Count() to the line and it looks like each combination of Ticket and Times record is being counted, and so I assume the same is happening with Sum().

Edited April 26, 2013 by David Jondreau

[Wim Decorte]

are the Total and SubTotal fields calculations or summary fields?