Query with Distinct Count

Dear All,

I have a question about using the Count function.  I'm trying to count the number of records in Field 4 for each combination of field1, field 2 and field3.

The input table -

Input Table to Query
The output I'm looking for-

Desired Output
Below is my query it seems to counting the number of records in field 4 and assigning it to field2, instead of breaking it down according to field3.    

SELECT [LocationData].Field1, Count([LocationData]. Field4) AS Field4_Count, MonitoredData.Field2, MonitoredData.Field3
FROM [LocationData] INNER JOIN MonitoredData ON [LocationData].Field2 = MonitoredData.Field2
GROUP BY [LocationData].Field1, MonitoredData.Field2, MonitoredData.Field3
HAVING ((([LocationData].Field1) Is Not Null));

Open in new window


I thought the names would make it easier to explain now I don't think so!

Thanks
AndyC1000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Maybe you just need to rearrange the field sequence:

SELECT
    [LocationData].Field1,
    MonitoredData.Field2,
    MonitoredData.Field3,
    Count(*) AS Field4_Count

/gustav
0
HainKurtSr. System AnalystCommented:
try this:

select field1, field2, field3, count(1) as cnt
from mytable
group by field1, field2, field3
0
PatHartmanCommented:
I don't see anything wrong with the query although, I would use a where clause rather than a having for this particular condition.  The WHERE clause is applied BEFORE any aggregation and the HAVING clause is applied AFTER aggregation so HAVING usually refers to something that was aggregated so you can sum amounts for example and select only the rows that have a net > 0.  Field1 isn't aggregated so it would be the same going in as coming out so get rid of it at the beginning.

Also Count(somefield) doesn't do what you think it does.  Count(somefield) eliminates nulls so
fld1, fld2, fld3
a, 1, x
a, 1, null
a, 1, y

the count would be
a, 1, 2

but if you use Count(*), the count would be
a, 1, 3
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.