Joining MySQL Tables and Calculating Counts - Output in PHP?

SELECT LOCATION, COUNT(1) as numberOfComps FROM COMPUTERS c LEFT JOIN COMPUSE cu ON c. COMPNAME = cu. Computer WHERE logofftime IS NULL GROUP BY LOCATION.

Thanks ONe. I'm having the same problem with this query as Brad's. This seems to output the number of computers that are in use.

I need to subtract the in-use computers (where logofftime is null) from the master table defining all computers in the building. Any thoughts? – mlibjh May 2 at 15:02 I've updated it.

Try now – ONe May 2 at 22:05 Thanks again, ONe. See my comments to Brad below. Logofftime cannot be set to IS NOT NULL, because there are several thousand records in the table -- one for each time a computer is used.

– mlibjh May 2 at 23:07 If I understood correctly, you want to get the number of FREE computers which are marked with null in that table. I reproduced your tables, and executing my first suggested query (the one with IS NULL), Im getting the expected result, so either you have to explain it again, or you have to check your code :) Im updating my suggested query to its original state, because it's the one who works. – ONe May 2 at 15:19 Sorry for my slow reply.

I've been out of town. I think you get the expected result because there were exactly four of each (null and not null) in my simplified example. I've updated the example above to better reflect the actual data.(I pulled the last three record from ten computers to better illustrate the problem.) I think by reproducing this table, you'll see what I mean.

Also, see my own answer below. I've solved the problem to some extent with PHP, however, I would like to be able to structure a MySQL query to do the same thing. – mlibjh May 2 at 22:59.

I did accomplish this task through some PHP calculations based on two separate MySQL queries. This works, but I would prefer to structure an SQL query that does the same thing. Here's the PHP: %s: %s computer available.", $totCompsKey, $availFloor); } else { printf("%s: %s computers available.

", $totCompsKey, $availFloor); } } else { printf("%s: all computers in use. ", $totCompsKey); } next($totCompsArray); }?

Select location, count(compid) as available from computers c1 inner join compuse c2 on c1. Compname = c2. Compname where logoff time is not null group by location.

Thanks for the help, Brad. This outputs the number of computers in use, not the number available. Any suggestions for subtracting the in-use computers from those in the COMPUTERS table?

– mlibjh May 2 at 14:58 Changed the query for you to look for IS NOT NULL on LOGOFF field. – Bradley May 2 at 21:28 That doesn't work either. The COMPUSE table is dynamically updated over months each time a user logs on or off, so there are thousands of records in that table.

When you set logofftime to IS NOT NULL, it includes all the other records and shows nearly 200,000 computers being available! I think there needs to be a way to subtract the count of logged in computers from the total count of computers within the grouping of location. Any thoughts?

Thanks for you help! – mlibjh May 2 at 22:04.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.