[Solved] SQL Access ” Count “? [closed]

by


You can’t use Count as it will do just that – count the number of records for each Name ignoring the value of tap.

But you check for the values of tap and sum these matches. However, while SQL Server returns 1 for a match, Access returns -1, thus – for a universal solution – you can apply Abs to always have a positive sum.

Then you get a query like this:

Select
    [Name],
    Abs(Sum(tap=1)) As Count1,
    Abs(Sum(tap=2)) As Count2,
    Abs(Sum(tap=3)) As Count3
From
    YourTable
Group By
    [Name]

Output will be:

Name  Count1  Count2  Count3
aa         2       1       1
bb         0       2       1
cc         1       1       0

5

solved SQL Access ” Count “? [closed]