How do I query number of files using Delphi firedac

Question

I am trying to gather data from 3-5 files and query select average, stddev and group by

I tried to make FdMemTable and put all data in one memtable. But I'm stuck from there.

Are there any way to query Memtable or are there way to do query multi files?

For example, I have number of db file that has data of product ID and score in it. Each file has data of each month (2016 Jan, 2016 Feb, 2016 march, 2016 April, ...)

What I want to do is to calculate average and stdDev of score for the period using query group by product ID.

At first I tried to use join but number of file varies so I could not use join query. So I have input all 4 data in 1 fdmemtable. But fdmemtable seems not to take SQL.

If its able to query fdmemtable then it will be best, or there is other way to query multiple files (number of file can change from 1 to any number) that will be good too.

2016.Jan

ProductID | CustomerScore | MarketScore
----------+---------------+------------
    AAA   |     70        |    72
    AAB   |     72        |    35
    AAC   |     73        |    90

2016.Feb

ProductID | CustomerScore | MarketScore
----------+---------------+------------
  AAA     |     72        |     74
  AAB     |     70        |     39
  AAC     |     77        |     92

Answer 1

Write a stored procedure that will access those databases storing intermediate results. Your DBMS just needs a support for iterating databases.