SQL Server Query with Percentage of Two Aggregated Values
How to write a single query to perform search based on a percentage criteria of two aggregated values?
For example, I have multiple pools of registration numbers in SQL Server database. Each registration number has a status of "Available" or "Registered". And I want to write a single query to find all pools that has less 20% available registration numbers?
One option to write a single query to perform on percentage of two aggregated values is to use inline views and apply the percentage criteria on the outer view.
if we have the following tables for your registration number example:
POOL table holds the list of pools with pool_id and name columns.
CODE table holds the list of registration numbers for each pool with pool_id, status, and value columns.
The search query to find all pools that has less 20% available registration numbers would be:
SELECT tt.* FROM (SELECT t1.pool_id, t1.name, t1.total, t2.available, CASE WHEN total=0 THEN 100 ELSE 100*available/total END ratio FROM (SELECT p.pool_id, p.name, COUNT(*) total FROM pool p, code c WHERE p.pool_id = c.pool_id GROUP BY p.pool, p.name ) t1, (SELECT p.pool_id, COUNT(*) available FROM pool p, code c WHERE p.pool_id = c.pool_id GROUP BY p.pool AND c.status='available' ) t2 WHERE t1.pool_id = t2.pool_id ) tt WHERE tt.ratio <= 20
In the query, "t1" view represents list of pools with total registration numbers in each pool. "t2" view represents list of pools with total available registration numbers in each pool. "tt" view combines "t1" and "t2" to represent a list of pools with total and available registration numbers, plus the percentage ratio of the two.
2016-06-12, 581👍, 1💬
What Is card game FreeCell? FreeCell is a card solitaire game played with a single deck. The key to ...
What do those Fitbit icons mean? Here is a list of icons used by Fitbit devices and website: (Runnin...
How to set column to be AUTO_INCREMENT in MySQL? And how to set its start value and increment value?...
What are built-in styles in Microsoft Word? Microsoft Word come with over 100 built-in styles that c...
I have an Apple iPad Air Model MD788C/A. Is it good? Yes. Apple iPad Air Model MD788C/A is a very go...