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?
 
PatHartmanConnect With a Mentor Commented:
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
 
Gustav BrockConnect With a Mentor CIOCommented:
Maybe you just need to rearrange the field sequence:

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

/gustav
0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
try this:

select field1, field2, field3, count(1) as cnt
from mytable
group by field1, field2, field3
0
All Courses

From novice to tech pro — start learning today.