Hello all,

I am semi-new to SQL and am trying to build a query. I have two tables:

[Routes]
RouteID: int
Status: int

[Meters]
MeterID: int
RouteID: int
Reading: real

I would like to select Routes.Status and a count for the total number of Meters and a count for the unread meters (Reading = NULL) where Meters.RouteID = Routes.RouteID. My Current SELECT statement looks like:

The problem is that I am getting the non-null count but I need the null count. Any ideas?

Thanks
-fakepoo

I suppose you could simply calculate the difference as `TotalUnread`. Maybe something like this:


Of course I have no way of (really) testing this SQL but it gives you an idea to simply insert a simple subtraction to get the total unread meters.

__________________
Custom BB codes you can use here:
[HTML] | [C++] | [CSS] | [JAVA] | [PY] | [VB]

Thank you admin. Clearly, I have a long way to go but this helped me over a hump. I'm sure I'll have more questions along the way.

OK. Now I have another problem. I have decided that each Meter now belongs to an Address record instead of a Route record. Routes will now contain Address records.

[Routes]
RouteID: int
Status: int

[Addresses]
AddressID: int
RouteID: int
Address: nvarchar

[Meters]
MeterID: int
AddressID: int
Reading: real

Now, I would like to get the number of addresses per route, the number of meters per route, and the number of unread meters per route.

I am, instead, getting really large numbers like it is counting every meter that is equal to another meter or something.

Thanks,
~fakepoo

Sorry, but there was not a problem with my query. My database had duplicate AddressID values and that was causing weird results.