Query with individual dates, counting values by criteria

Question

I want a query that only shows individual dates (so no single date appears twice/multiple), counts the number of records with given date and counts further the number of records with another field fulfilling other criteria:

Date    | total amount | Field2 = "Aaa" | Field2 = "Bbb" | Filed2 = ....
10.7.18 | 32           | 2              | 3              | ...
11.7.18 | 20           | 5              | 2              | ...

My approach is a query with its property set to "unique values" to "yes", than I added the date-field two times, first with its function set to "group by", the second with its function set to "count"; this gives me my individual dates only and the number of records with the set date. SO far so good.

Now I add "Field2", set its function to "count", and add the criteria to "=Value("Aaa")". This brings up an empty list of records.

Answer 1

Try this:

Select
    [Date],
    Sum(Amount) As TotalAmount,
    Sum(Abs(Field2 = "Aaa")) As CountA,
    Sum(Abs(Field2 = "Bbb")) As CountB,
    Sum(Abs(Field2 = "Ccc")) As CountC
From
    YourTable
Group By
    [Date]

If all you need is a total count, use:

Select
    [Date],
    Count(*) As TotalCount,
    Sum(Abs(Field2 = "Aaa")) As CountA,
    Sum(Abs(Field2 = "Bbb")) As CountB,
    Sum(Abs(Field2 = "Ccc")) As CountC
From
    YourTable
Group By
    [Date]