I haven't done too many nested statements in MySQL and I was hoping to have the below SQL looked at and let me know if there is a better/more efficient way of doing what I am trying to accomplish.Through all of my tests it appears that everything is working properly, I just want to make sure I am meeting best practices, etc.SELECT DISTINCT    -- Fab A with EMC    -- Conditions: Anything with an EMC    (SELECT            count(`wo_num`)        FROM            `work_orders`        WHERE            `emc` = '1'                AND `auth_status` = 'ACTIVE') as `a_count_e`,    -- Fab A Without EMC    -- Conditions: NO EMC, Contract price greater than 10,000, NOT Time and Material, NOT Service Order, NOT Contract Install    (SELECT            count(`wo_num`)        FROM            `work_orders`        WHERE            `emc` = '0'                AND replace(replace(`contract_price`, ',', ''), '$', '') >= 10000                AND `auth_status` = 'ACTIVE'                AND `contract_install` = '0'                AND `terms` <> 'TIME AND MATERIAL'                AND `wo_type` <> 'SERVICE ORDER') as `a_count`,    -- Fab B    -- Conditions: NO EMC, Contract price between 1500 and 9999, NOT Time and Material, NOT Service Order, NOT Contract Install    (SELECT            count(`wo_num`)        FROM            `work_orders`        WHERE            `emc` = '0'                AND replace(replace(`contract_price`, ',', ''), '$', '') >= 1500                AND replace(replace(`contract_price`, ',', ''), '$', '') <= 9999                AND `auth_status` = 'ACTIVE'                AND `contract_install` = '0'                AND `terms` <> 'TIME AND MATERIAL'                AND `wo_type` <> 'SERVICE ORDER') as `b_count`,    -- Small    -- Conditions: NO EMC, Contract price between 600 and 1499, NOT Time and Material, NOT Service Order, NOT Contract Install    (SELECT            count(`wo_num`)        FROM            `work_orders`        WHERE            `emc` = '0' AND `auth_status` = 'ACTIVE'                AND `wo_type` <> 'SERVICE ORDER'                AND `contract_install` = '0'                AND `terms` <> 'TIME AND MATERIAL'                AND replace(replace(`contract_price`, ',', ''), '$', '') >= 600                AND replace(replace(`contract_price`, ',', ''), '$', '') <= 1499) as `sm`,    -- XX    -- Conditions: NO EMC, Contract price less than 600, NOT Time and Material, NOT Service Order, NOT Contract Install    (SELECT            count(`wo_num`)        FROM            `work_orders`        WHERE            `emc` = '0' AND `auth_status` = 'ACTIVE'                AND `wo_type` <> 'SERVICE ORDER'                AND `contract_install` = '0'                AND `terms` <> 'TIME AND MATERIAL'                AND replace(replace(`contract_price`, ',', ''), '$', '') <= 599) as `xx`,    -- TM    -- Conditions: NO EMC, NOT Service Order, NOT Contract Install    (SELECT            count(`wo_num`)        FROM            `work_orders`        WHERE            `emc` = '0'                AND `auth_status` = 'ACTIVE'                AND `contract_install` = '0'                AND `wo_type` <> 'SERVICE ORDER'                AND `terms` = 'TIME AND MATERIAL') as `tm`,    -- Contract Install    -- Conditions: NO EMC, IS Contract Install    (SELECT            count(`wo_num`)        FROM            `work_orders`        WHERE            `emc` = '0'                AND `auth_status` = 'ACTIVE'                AND `contract_install` = '1') as `ci`,    -- Service    -- All NO EMC, IS Service Order, NOT Contract Install    (SELECT            count(`wo_num`)        FROM            `work_orders`        WHERE            `emc` = '0'                AND `wo_type` = 'SERVICE ORDER'                AND `auth_status` = 'ACTIVE'                AND `contract_install` = '0') as `service`,    -- Total count    -- All Active orders    (SELECT            count(`wo_num`)        FROM            `work_orders`        WHERE            `auth_status` = 'ACTIVE') as `total`FROM    `work_orders`WHERE    `auth_status` = 'ACTIVE'Results: Side Note:Forgive me for the`replace(replace(`contract_price`, ',', ''), '$', '')`When the application was originally written the contract prices were stored as dollar amounts with $ and ,, so I have to remove them for numerical comparisons.
Nested SQL Statement
mysql;sql
The first thing to comment on, requires bold (sorry for shouting....) store numbers as numbers, not strings!!! @Malachi is right that replace(replace(contract_price, ',', ''), '$', '') is inefficient, and should not be repeated, but it would be even better for it to not exist at all.... your contract_price column should be an integer, or decimal value if you have cents.If you do have cents, then you should know that you may be missing values. The statements like:AND replace(replace(`contract_price`, ',', ''), '$', '') >= 600Will convert both sides of the comparison to floating-point values, and, there may be values with contract_price of $599.50 ... right, and that will not be >= 600. We expect $599.50 to fall in to the bucket below that, but that bucket has the condition:AND replace(replace(`contract_price`, ',', ''), '$', '') <= 599and $599.50 is not <= 599....To get the comparisons right, you need to compare to < 600. THat way, you won't have gaps in your buckets.Then, the select statement you are running is very inefficient. You can reduce it to a single query, with conditional values in the select. Consider the following:SELECT    -- Fab A with EMC    -- Conditions: Anything with an EMC    SUM(case when `emc` = 1 then 1 else 0 end) as `a_count_e`,    -- Fab A Without EMC    -- Conditions: NO EMC, Contract price greater than 10,000, NOT Time and Material, NOT Service Order, NOT Contract Install    SUM(case when `emc` = '0'                AND `contract_price` >= 10000                AND `contract_install` = '0'                AND `terms` <> 'TIME AND MATERIAL'                AND `wo_type` <> 'SERVICE ORDER'        then 1 else 0 end) as `a_count`,    -- Fab B    -- Conditions: NO EMC, Contract price between 1500 and 9999, NOT Time and Material, NOT Service Order, NOT Contract Install    SUM(case when `emc` = '0'                AND `contract_price` >= 1500                AND `contract_price` < 10000                AND `contract_install` = '0'                AND `terms` <> 'TIME AND MATERIAL'                AND `wo_type` <> 'SERVICE ORDER'        then 1 else 0 end) as `b_count`,    -- Small    -- Conditions: NO EMC, Contract price between 600 and 1499, NOT Time and Material, NOT Service Order, NOT Contract Install    SUM(case when `emc` = '0'                AND `contract_price` >= 600                AND `contract_price` < 1500                AND `contract_install` = '0'                AND `terms` <> 'TIME AND MATERIAL'                AND `wo_type` <> 'SERVICE ORDER'        then 1 else 0 end) as `sm`,    -- XX    -- Conditions: NO EMC, Contract price less than 600, NOT Time and Material, NOT Service Order, NOT Contract Install    SUM(case when `emc` = '0'                AND `contract_price` < 600                AND `contract_install` = '0'                AND `terms` <> 'TIME AND MATERIAL'                AND `wo_type` <> 'SERVICE ORDER'        then 1 else 0 end) as `xx`,    -- TM    -- Conditions: NO EMC, NOT Service Order, NOT Contract Install    SUM(case when `emc` = '0'                AND `contract_install` = '0'                AND `terms` = 'TIME AND MATERIAL'                AND `wo_type` <> 'SERVICE ORDER'        then 1 else 0 end) as `tm`,    -- Contract Install    -- Conditions: NO EMC, IS Contract Install    SUM(case when `emc` = '0'                AND `contract_install` = '1'        then 1 else 0 end) as `ci`,    -- Service    -- All NO EMC, IS Service Order, NOT Contract Install    SUM(case when `emc` = '0'                AND `wo_type` = 'SERVICE ORDER'                AND `contract_install` = '0'        then 1 else 0 end) as `service`,    -- Total count    -- All Active orders    COUNT(*) as `total`FROM (select Cast(replace(replace(`contract_price`, ',', ''), '$', ''), Decimal(10,2)) as `contract_price`,             `emc`,             `contract_install`,             `wo_type`,             `terms`        from `work_orders`        WHERE `auth_status` = 'ACTIVE')Since changing trhe data type in the column is not feasible, the above query has been edited to do the conversion in just one pace...Here's the from/where clause if the table had numeric contract_price....(Replace the original from and where clause )FROM    `work_orders`WHERE    `auth_status` = 'ACTIVE'