AnsweredAssumed Answered

How to get Summary Field Right?

Question asked by MorkAfur on Mar 4, 2015
Latest reply on Mar 5, 2015 by MorkAfur

Title

How to get Summary Field Right?

Post

Doing some "hack analysis" on my server, I'm trying to get a list (single table) of fields that confirm to the following SQL Query:

SELECT
  max(DateOfHack) AS max_date,
  remoteIP,
  count(*) GroupCount
FROM "firewall fields"
WHERE YEAR (dateOfHack)= 2015
GROUP BY DateOfHack, remoteIP HAVING COUNT(*) > 5
ORDER BY GroupCount DESC, DateOfHack DESC

Now, although this query took only about 5 minutes to get right, trying to do this in FMP with FMP tools on a FMP layout is causing me problems.

In particular, I can't see quite how to get a summary field for a layout (like the group count of the individual IPs) that I can further limit via a FIND so I'm only looking at groups greater than a certain count.

Even with the data sorted, a calculation field for COUNT(remoteIP) or a summary field (also for count) both give the wrong answer (1 vs. the total record count).

This isn't a report, but a layout I'm trying to create for data analysis.

Thanks in advance,

- m

Outcomes