CODEWITHSUNDEEP
mysql
MaryCoding
MaryCoding

Reputation: 664

Tracking api calls with mysql query

I have a table named api_keys where I track api transactions. I been give 3 api keys by provider apiA each key allows for a maximum of 5000 calls. I am able to run separate queries to get total number of calls, api keys with < 5000 calls and api keys with 5000 calls. However, how can i place those queries in to one query that displays percentage of consumed and not_used? SQLFIDDLE

Desired output in percentage:

+----------+----------+
| Consumed | Not_used |
+----------+----------+
|       70 |       30 |
+----------+----------+

Total number of api calls

SELECT COUNT(*) * 5000 as total_api_calls FROM api_keys WHERE `source` = 'apiA';

Api keys that havent reached 5000 calls:

SELECT * FROM api_keys WHERE `source` = 'apiA' and `transactions` < 5000

Api keys that have reached 5000 calls: 

SELECT * FROM api_keys WHERE `source` = 'apiA' and `transactions` >= 5000;

Upvotes: 2

Views: 177

Answers (1)

Schwern
Schwern

Reputation: 164889

Two CASE statements will give each row used and not_used flags set to 1 or 0 appropriately. Then you can SUM them up and divide by the total number of rows to get the percentage.

SELECT `source`,
       SUM(CASE WHEN transactions <  5000 THEN 1 ELSE 0 END)/COUNT(*)*100 AS 'Not Used',
       SUM(CASE WHEN transactions >= 5000 THEN 1 ELSE 0 END)/COUNT(*)*100 AS 'Used'
FROM api_keys
WHERE `source` = 'apiA';

Upvotes: 1

Related Questions