Nested SQL Statement

By Posted on

Problem

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.

Solution

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`, ',', ''), '$', '') >= 600

Will 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`, ',', ''), '$', '') <= 599

and $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'

Get rid of your Commented Code it’s messy

This bit of Code might be shortened

AND replace(replace(`contract_price`, ',', ''), '$', '') >= 1500
AND replace(replace(`contract_price`, ',', ''), '$', '') <= 9999

to something like this, (if it works the way I think it should) it does work like I think it should.

AND REPLACE(REPLACE(`contract_price, ',',''),'$','') BETWEEN 1499 AND 10000 --(or 1500 AND 9999)

I know this works with Dates in SQL Server but I haven’t really tried it in MySQL as I don’t play with it much.

this would help performance in that you would only have to run the REPLACE function twice here instead of 4 times

EDIT:

my logic is a little flawed. the end points are semi ambiguous,

the statement should be something like the following:

AND REPLACE(REPLACE(`contract_price, ',',''),'$','') BETWEEN 1500 AND 9999

and if you aren’t holding Cents for this specific operation then:

AND REPLACE(REPLACE(`contract_price, ',',''),'$','') BETWEEN 1501 AND 9999

And the previous one would look like this

AND REPLACE(REPLACE(`contract_price, ',',''),'$','') BETWEEN 600 AND 1499.99

OR with out the Cents:

AND REPLACE(REPLACE(`contract_price, ',',''),'$','') BETWEEN 600 AND 1499

Where do you define column auth_status, you use it in the WHERE clause of the outside SELECT statement but it doesn’t exist as a column in the outer table that is built?

Leave a Reply

Your email address will not be published. Required fields are marked *