yet another count issue in a query (1 Viewer)

[Rat1sully]

apologies for creating a new thread about what seems to be a done to death topic on here but i've tried most of the solutions I can see from searching

I need a count of how many classes a piece of equipment is fitted to, at the moment i can get the number of different types of equipment fitted to a class but not vice vesa as that sums the number fitted on each vessel in each class rather than just the number of classes the equipment is fitted to

SELECT tblMEList.[Equipment Tag], Count(tblClass.Class) AS CountOfClass
FROM ((tblCat INNER JOIN tblMEList ON tblCat.[GCFS Cat] = tblMEList.[GCSF Cat]) INNER JOIN tblShipFitData ON tblMEList.[Equipment Tag] = tblShipFitData.Equipment) INNER JOIN (tblClass INNER JOIN tblPlatformList ON tblClass.Class = tblPlatformList.Class) ON tblShipFitData.Vessel = tblPlatformList.Vessel
GROUP BY tblMEList.[Equipment Tag]
ORDER BY tblMEList.[Equipment Tag];

Edit: as things stand if I make the count a normal group by I can clearly see that for the first piece of equipment there are 7 classes listed but yet it sums the total number fitted not the number of classes fitted to

edit2: and i'm trying to do it in 1 query not 2 to reduce clutter

 

[Pat Hartman]

You need to include Class in the group by if that is what you want to count. Queries always count the number of rows. They don't count the number of distinct values. You need to get a list of distinct values and then you can count them.

Create a second query that counts the first one and this time don't include class in the group by.

BTW - Count(*) is far more efficient than Count(somename) because the latter requires that the query engine read each selected row from the table (or index if this is an indexed field) to determine if the field is null because nulls aren't counted. In most cases you don't care about the distinction and so the * will let the query engine optimize the count however it can.

 

[Rat1sully]

ta I thought it would be a case of implimenting it over 2 queries which is what i had it doing already, I really need to stop posting questions on here then answering them myself before ppl reply lol