How to do Count

BIZ TMS Aurelius
1

Hi,

I want to do the following sql query

SQL :
SELECT COUNT(DISTINCT ( BarCode)) as 'Antal', MONTH(scanntime) as Month FROM scannstatistic where (YEAR(scanntime) = 2018)
GROUP by MONTH(scanntime)

This is working very well on a MaraDB 10.2.6
But I want to put it in the Aurelius framework as I have the rest of the apps db handling in that.

After reading the Aurelius documentation I came up with the following, but it will not even compile!
What misstakes have I donne?

type
   TCountM = class        // just for the example
    public
     Antal : Integer;
     aMonthnbr : Integer;
   end;


procedure ProductionPerMonth;
var
  Mgr: TObjectManager;
  LScannstatistic  : TScannstatistic;
  C : Integer;
  M: Integer;
  Values : TObjectList<TCountM>;
begin
   Mgr := TObjectManager.Create(ThreadConnection);
   Values :=  Mgr.Find<TScannstatistic>
                   .Select(Linq['BarCode'].Count)       // string Field with barcodes
                   .Where(Linq['scanntime'].Year = 2017)   // A TDateTime field
                   .Add(TProjections.Group(Linq['scanntime'].Month))
                  .List;

     C :=  Values[0].Antal;
     M :=  Values[0].aMonthnbr;

BR Ake

2

You should use ProjectionList to retrieve more than one projection, and when a query returns projections, it returns TCriteriaResult object. This is explained in the following topics of the documentation:

http://www.tmssoftware.biz/business/aurelius/doc/web/results_with_projections.html
http://www.tmssoftware.biz/business/aurelius/doc/web/projectionlist.html

Here is the code that should compile:


var
  Mgr: TObjectManager;
  LScannstatistic: TScannstatistic;
  C : Integer;
  M: Integer;
  Values : TList<TCriteriaResult>;
begin
   Mgr := TObjectManager.Create(ThreadConnection);
   Values :=  Mgr.Find<TScannstatistic>
     .Select(TProjections.ProjectionList
       .Add(TProjections.Group(Linq['scanntime'].Month).As_('Antal'))
       .Add(Linq['BarCode'].Count.As_('aMonthnbr'))
     )
     .Where(Linq['scanntime'].Year = 2017)
    .ListValues;


   C := Values[0]['Antal'];
   M := Values[0]['aMonthnbr'];
end;
3

Thank you,
Now it works
BR Ake