CODEWITHSUNDEEP
mysql
A_Elric
A_Elric

Reputation: 3568

SQL Query to get percentage from another table

So, I have two tables, one is a "current" table, the other is a "historical" table. My existing query looks a bit like this

$QueryStr="SELECT *, IF(HOUR(TIMEDIFF(NOW(), TIME)) >= 1, 1, 0) AS OlderThanAnHour from ddCurrent";

What I want to add is a select from ddHistorical that counts the total number of records and then presents the number that aren't equal to "OK"

Schema Structure:

ddCurrent:
-ID
-LOCATION
-STATUS
-TIME

ddHistorical
-ID
-CID (same as ID from ddCurrent)
-LOCATION
-STATUS
-TIME

So in essence what I want is a join to do a percentage of when it says "OK" in Status to when it says ANYTHING else for each ID.

Thanks

Upvotes: 1

Views: 231

Answers (1)

Menelaos
Menelaos

Reputation: 26040

$QueryStr="SELECT count(*) / 
(SELECT count(*) from ddHistorical) 
from ddHistorical where not status = 'OK';

The query above calculates the total number of historical records in a sub query. Then the total count of queries with not OK status are divided by the result from the subquery.

Upvotes: 1

Related Questions