4

This seems like a regular thing to do, but I can't seem to find how to do it.

I have a join query

 SELECT a.nom_batim, COUNT(b.maxten) AS NumFaulty
 FROM tblTrials AS b, tblRooms AS a
 WHERE b.batiment = a.batiment
 AND b.maxten > 10
 GROUP BY a.nom_batim
 ORDER BY a.nom_batim

that should only return a count of the tblTrials entries. However, since I don't know how to code that, it's currently counting all occurances of b.maxten > 10, TIMES all occurances of b.batiment = a.batiment. I have 1 actual occurance of b.maxten > 10 in the table, but 231 occurances of b.batiment = a.batiment (the tables are set up badly, not my choice; these tables are considered read-only to me), so it returns a count of 231.

How do I COUNT(b.maxten) correctly, but still display a.nom_batim as a user-friendly representation of the batiment ID field? (a.nom_batim is the long name for the building #batiment)

UPDATE
This is what I ended up doing so far..

SELECT a.nom_batim, COUNT(b.batiment) AS NumFaulty
FROM (SELECT DISTINCT nom_batim, batiment FROM tblRooms) AS a
INNER JOIN tblTrials AS b ON a.batiment = b.batiment
WHERE b.maxten > 10
GROUP BY a.nom_batim
ORDER BY a.nom_batim

It works but seems like a resource hog when I only need max ~30 values from tblRooms, but have to query all 5000+ rows selecting only distinct batiment values. Is there no way to do this without having a batiment table tblBatiment: batiment, nom_batim I know it's the best way but I don't have the access.

StuckAtWork
  • 1,613
  • 7
  • 23
  • 37

5 Answers5

1

You can perform the count in a sub-query so it only applies to the one table's records:

SELECT ..
FROM (SELECT batiment, COUNT(maxten) FROM tblTrials WHERE maxten > 10) AS b
    ,tblRooms AS a
...

Otherwise, the count is applying across all records in the final result, because the query engine doesn't differentiate between records coming from one place or another in a COUNT.

StuckAtWork
  • 1,613
  • 7
  • 23
  • 37
mellamokb
  • 56,094
  • 12
  • 110
  • 136
  • I came across this just after posting actually (seems to happen), but thought maybe the extra query would diminish performance. There doesn't seem to be another easy way though.. – StuckAtWork Jun 01 '12 at 13:28
  • It shouldn't be too inefficient. All the records will be grouped and counted once, then combined once for the result. You can review the query plan if you want to be sure. – mellamokb Jun 01 '12 at 13:30
  • Turns out you can't do it this way around.. it expects `batiment` as part of the aggregate function which is unfriendly to the user. I need to have the nom_batim populated using FROM (SELECT...), not have the FROM (SELECT...) be on tblTrials. No idea why but it works. – StuckAtWork Jun 01 '12 at 13:54
1

Going back to your original query, you can get what you want if you have an identity column on the tblTrials table:

SELECT a.nom_batim, COUNT(distinct b.id) AS NumFaulty
FROM tblTrials b INNER JOIN tblRooms a   
     ON b.batiment = a.batiment
WHERE b.maxten > 10
GROUP BY a.nom_batim
ORDER BY a.nom_batim

I also replaced your join syntax with the correct join syntax (using the "join" keyword).

StuckAtWork
  • 1,613
  • 7
  • 23
  • 37
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I do not have one proper index, it is a combination of 3 fields. Is there a way to COUNT(distinct b.fieldA, b.fieldB, b.fieldC)? Again, database design not my fault, I just have to program over it. – StuckAtWork Jun 01 '12 at 15:23
  • You can join the fields together and count that, something like count(distinct fieldA+';'+fieldB+';'+fieldC). That was SQL Server syntax, and I don't know how strings are concat'ed in Access. Also, you may have to use the cast() function to convert non-character data types to characters. – Gordon Linoff Jun 01 '12 at 15:27
  • More or less efficient than what? The efficiency of a query depends highly on the SQL optimizer. This would seem to do two table scanes, one join, one aggregation, and additional work for the distinct. Plus, the join is an equi-join, which you can optimize by putting in indexes. There is no reason to think that this would perform worse than other constructs that return the same results. – Gordon Linoff Jun 01 '12 at 17:52
0

Try to use:

HAVING b.maxten>10
Monolo
  • 18,205
  • 17
  • 69
  • 103
SoEnLion
  • 185
  • 5
0

Try this:

 SELECT a.nom_batim, COUNT(b.maxten) AS NumFaulty
 FROM tblTrials AS b, tblRooms AS a
 WHERE b.batiment = a.batiment
 GROUP BY a.nom_batim
 HAVING count(b.maxten) > 10
 ORDER BY a.nom_batim
aF.
  • 64,980
  • 43
  • 135
  • 198
  • I don't believe it's correct.. results in an error. Since I can't aggregate out b.maxten (since the count would then produce a list of maxten's, and always a count of 1), this doesn't work. – StuckAtWork Jun 01 '12 at 13:27
  • Sorry, missed the count on it! – aF. Jun 01 '12 at 13:36
  • Now it returns only buildings which have more than 10 maxten entries. =P Maxten is a simple name for a simple trial condition I'm doing; it means the number field `maxten`'s value should be less than 11, not that a building needs less than 10 entries of this field. Sorry for the confusion. – StuckAtWork Jun 01 '12 at 13:38
0

The best I could do so far which works:

SELECT a.nom_batim AS Building, Count(q.batiment) AS Fixes
FROM (SELECT DISTINCT nom_batim, batiment FROM tblRooms)  AS a 
INNER JOIN tblTrials AS q 
ON a.batiment = q.batiment
WHERE q.maxten > 10
GROUP BY a.nom_batim

Seems like the SELECT DISTINCT nom_batim, batiment FROM tblRooms would be slow considering tblTrials may contain 60k entries and tblRooms may contain 10k entries.. but the entries aren't input yet so I can't really test it. Gordon made a good point that if it's returning the same stuff, it's likely ~the same speed. I do have multi-field primary keys so it might help things along as well (not as much as an ID field perhaps but what can you do).

Thanks to others that answered.

StuckAtWork
  • 1,613
  • 7
  • 23
  • 37