Equivalent of SQL Group by and Sum

[El_Pablo]

Hi,

I'm having a problem conceptualizing an SQL query in FM.

How would you do the equivalent of a SQL Group BY and SUM in FM?

Let say I have a single table:

FILES (pkFileID, fkDayID, FileCode, WorkedTime)

pkFileID --> Primary key

fkDayID --> Foreign key

FileCode --> A Code such as "GE-TR-123"

WorkedTime --> A number which represents the work time on a file.

In SQL would do:

SELECT Files.FileCode, SUM (Files.WorkedTime) as TotalWorkedTime

FROM Files

GROUP BY Files.FileCode

This would give me a list with the amount of work time for each FILE.

How can I get the equivalent in FM?

Thank you

[mr_vodka]

Look into subsummary reports. You can group by fields. Create a new columnar list/report layout and follow the wizard.

[El_Pablo]

I tried all the possible ways with the wizard and I am not able to get the wanted solution. When I do grouping, how do a I get the sum?

Here is what I get:

As you can see, the total time per file is not good since it is a repetition of the summary field totalTime. And the values are not grouped.

Is it possible to create a SQL Group-by Sum? Like I posted in the first post.

[Søren Dyhr]

Perhaps if you'd used the wizzard, you could have seen which data you needed to allocate to where and why?

But as it is should your summary field should be moved to a subsummary part, say - next to you filecode field instead of being in the body as it is on your image. Perhaps what you really want is to get rid of the body-part ...then delete it after moveing the summaryfield to it's correct location.

--sd

[El_Pablo]

Thanks to both of you! It worked in the subsummary field, although I still prefer SQL method. The only thing is that I had to hide the body, since it was not useful...

Edited February 5, 2007 by Guest