AnsweredAssumed Answered

Trying to get a simple count report

Question asked by MorkAfur on Jan 16, 2015
Latest reply on Jan 17, 2015 by philmodjunk

Title

Trying to get a simple count report

Post

Since FMP does not allow an expression in a GROUP BY clause, I've tried to create some intermediate fields, but I always get to the point where FMP just won't give me the listing I want.

Say you have two fields: a date field called "datefield" and a vendor id field called "vendorID" (we don't need the vendorID field for this query).

In MySQL, the query, because I don't have to include everything in the select into the GROUP BY (unlike in FMP), this listing report took me maybe 2 minutes.

SELECT
    YEAR (datefield) AS YEAR,
    count(dateField) AS cnt
FROM
    fliers
GROUP BY
    YEAR
ORDER BY
    YEAR DESC , COUNT(datefield) DESC

To give me a simple list of counts by year

2015    2

2013   2

2012   1

------------------

In FMP, trying to do the same thing is difficult at least for me today.

Time spent so far: 3 hours!   Success: None.

In the actual FMP portal table, I have fields with the full date each of these portal records was created. That "should" be enough for a count by this date. This is a "simple" one table query. I'm just trying to get a simple SQL to feed to the graph SQL so I can get a bar chart with these counts by year.

I've tried creating an intermediate field like YEAR(FullDateField) and that gives me, as expected,

2015

2014

etc.

So far, so good...

Yet, then to get the actual counts of the records in the same table for each of these years, I get totally stuck. If I try to create another fields like COUNT(YEAR(FullDateField)), FMP barfs.

--------

Would appreciate any suggestions how to get this simple listing report out of FMP. I could create a program and do it that way, but then, as far as I know, I can't feed that to the graph.

Please let me know this posting isn't clear.

Look forward to any suggestions.

Thanks,

- m

Outcomes