Nested SQL Statement

Question

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.

Do you need 1 row that is returned or multiple rows may also? — chillworld, May 13 at 5:09

rolfl · Accepted Answer · 2014-05-13 13:50:16Z

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'

I am VERY aware of never storing numerical values as Strings, inheriting projects is not always friendly. I would love to run a quick little script to turn every entry's contract price into a numerical value but I am not being given the time to work on this project in order to make sure the front end shows it properly and remove and server side concoction the other dev may or may not have used — Asok, May 13 at 12:11
@Asok, What data type is contract_price ? looks like you can change the column type without losing data. you just have to make sure that you don't have any null values hiding in there — Malachi, May 13 at 13:42
Need to remove all instances of CASE after END, otherwise it throws a 1064 - ..MySQL server version for the right syntax to use near 'case) as a_count_e,... I ran your version (with contract price as a string still) vs my/Malachi's nested SQL on the full data pool (not restricting to active orders) in MySQL workbench and it resulted in 0.109 vs 0.110 duration, maybe my data set isn't large enough to see a difference or I am not properly bench-marking? 9000 records. — Asok, May 13 at 13:44
@malachi Unfortunately contract_price is stored as a varchar(100) with dollar signs and commas, I can't change it at this point. — Asok, May 13 at 13:47
@malachi Changing the table would take minutes for me. The problem is that the previous programmer hated OOP and has contract_price values manipulated, displayed and everything else you can think of in a ton of different places. Luckily I did convince my boss to let me re-write a good chunk of it but I have to finish this reporting project and a couple others first. I will get around to it, trust me. I am OCD about consistency and best practices, which is partly why I posted this question. — Asok, May 13 at 14:04

Malachi · Answer 2 · 2014-05-13 14:10:01Z

up vote 3 down vote

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?

edited May 13 at 14:10

answered May 12 at 17:47

Malachi
9,26731868

1

Exactly what I was looking for. Thank you. Comments are not in my actual code, they are there to break apart the SQL in the OP to (what I had hoped) read easier. – Asok May 12 at 17:51

1

Regarding auth_status, that is a good question, that is a column in the work_orders table, this entire SQL statement is pulling from the single table. I don't know if I need that since it is in the inner sql statements? – Asok May 12 at 17:54

1

doing a WHERE when you don't need to can occasionally have adverse side-effects to the performance of the query. – Malachi May 12 at 17:57

1

This is great information, thank you. I removed the external WHERE and it works great. I am going to try your suggestions on the contract_price comparison. Thanks again. Marking as accepted. Any other suggestions is greatly appreciated. – Asok May 12 at 18:00

add a comment |

asked	2 months ago
viewed	74 times
active	2 months ago

current community

your communities

more stack exchange communities

Nested SQL Statement

2 Answers 2

EDIT:

Your Answer

Not the answer you're looking for? Browse other questions tagged mysql sql or ask your own question.

Hot Network Questions

current community

your communities

more stack exchange communities

Nested SQL Statement

2 Answers 2

EDIT:

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysql sql or ask your own question.

Related

Hot Network Questions