Why does the count function in SQL seem to be doing more than counting the column I ask it to?

Question

I have an INSERT query that is pulling data from two tables and inserting that data into a third table. Everything appears to be working fine except that the COUNT part of the query isn't returning the results I would expect.

The first set of tables this query runs is MIUsInGrid1000 (number of rows = 1) and Results1000 (number of rows = 24). The number that is returned from the Count part of the query is 24 instead of being 1 like I would have expected.

The next set of tables is MIUsInGrid1000 (number of rows = 3) and Results1000 (number of rows = 30). The number that is returned from the Count part of the query is 90 instead of being 3 like I would have expected.

It appears that the product of the two counts is what is being returned to me and I can't figure out why that is. If I take out the references to the Results tables then the query works the way I would expect. I think that I am misunderstanding how at least some part of this works. Can someone explain why this is not working as I expected it to?

strQuery1 = "Insert Into MIUsInGridAvgs (NumberofMIUs, ProjRSSI, RealRSSI, CenterLat, CenterLong)  " & _
            "Select  Count(MIUsInGrid" & i & ".MIUID), Avg(MIUsInGrid" & i & ".ProjRSSI), Avg(MIUsInGrid" & i & ".RealRSSI), Avg(Results" & i & ".Latitude), Avg(Results" & i & ".Longitude) " & _
           "From MIUsInGrid" & i & ", Results" & i & " "

Answer 1

This should solve your immediate problem

Count(DISTINCT MIUsInGrid" & i & ".MIUID)

The naked COUNT function counts the non-NULL values, not the distinct values, unless you tell it to switch behavior by using DISTINCT.

When two tables are joined like you do it (you build a cartesian product), then the number of resulting rows is the number of rows in the one times the number of rows in the other table.

This leads me to the suspicion that you are missing a join condition.

Apart from that i find it bewildering that you have a number of obviously identical tables that are indexed by name. This most certainly is a substantial design flaw in the database.

Answer 2

It seems logical to me that if you are joining two tables, one with 1 row and the other with 24 rows that there, is the possibility, of having a result set of 24 rows.

I notice you have not included a WHERE clause in your SQL (perhaps for brevity), but if you don't have that you are doing a CROSS JOIN (or cartesian join) between the tables and this will provide unexpected results.

The COUNT function will count all rows in the database, to determine how many "distinct" ID's there are, you can use the answer provided by Tomalak

Answer 3

The way I usually figure these things out is to not use any aggregates first to see what my result sets will be. Then, I start adding in the aggregate functions.