AnsweredAssumed Answered

Aggregate records by two fields

Question asked by tuzand on Dec 31, 2017
Latest reply on Jan 1, 2018 by tuzand

I have the following 3 tables:

  • Color
  • Taste
  • Chocolate
    • Color_Fk
    • Taste_Fk
    • Brand: Text

I would like to create an aggregated list of the chocolates grouped by the color AND the taste, by counting the number of brands within that type (color+taste). Aggregation works if I use only one table, but then of course the other table will be disregarded. How can I do that with both two tables?

I also tried SQL solution, but the GROUP BY from joined tables does not seem to work in FM (query result: "?")

e.g.: Chocolate table

Color_FkTaste_FkBrand
11Brand1
11Brand2
12Brand1
21Brand1
22Brand1
22Brand2

 

I would like to have the following query result:

Color_FkTaste_FkCount(Brand)
112
121
211
222

 

I would really appreciate any help!

Thank you very much.

Outcomes