SUM CASE, COLUMN MINUS COLUMN

  • I have this code working fine. I need to subtract one column from another to get the corrected results. I tried qty-NewQty and the results were incorrect. Any ideas?

    select s.partNo, p.descript,

    sum(case when battState = '99' then qty else '0'end) as CLNT,

    sum(case when battState = '1' then qty else '0' end) as AVAIL,

    sum(case when battState = '20' then qty else '0' end) as QAH,

    sum(case when battState = '30' then qty else '0' end) as BOOST,

    sum(case when battState = '31' then qty else '0' end) as RESTING

    from batt_State s, pnLU as p

    where 0=0 and s.PartNo = p.PartNo

    group by s.partNo, p.descript

  • Where's [NewQty]?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • select s.partNo, p.descript,

    sum(case when battState = '99' then qty else '0'end) as CLNT,

    sum(case when battState = '1' then qty-newQty else '0' end) as AVAIL,

    sum(case when battState = '20' then qty-newQty else '0' end) as QAH,

    sum(case when battState = '30' then qty-newQty else '0' end) as BOOST,

    sum(case when battState = '31' then qty-newQty else '0' end) as RESTING

    from batt_State s, pnLU as p

    where 0=0 and s.PartNo = p.PartNo

    group by s.partNo, p.descript

  • So what exactly is the issue here?

    I would suggest you use the newer join constructs. With this query you don't even need a where clause.

    Here is the syntax:

    from batt_State s

    join pnLU as p on s.PartNo = p.PartNo

    group by s.partNo, p.descript

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • kabaari (5/1/2013)


    select s.partNo, p.descript,

    sum(case when battState = '99' then qty else '0'end) as CLNT,

    sum(case when battState = '1' then qty-newQty else '0' end) as AVAIL,

    sum(case when battState = '20' then qty-newQty else '0' end) as QAH,

    sum(case when battState = '30' then qty-newQty else '0' end) as BOOST,

    sum(case when battState = '31' then qty-newQty else '0' end) as RESTING

    from batt_State s, pnLU as p

    where 0=0 and s.PartNo = p.PartNo

    group by s.partNo, p.descript

    Which table is [newQty] from? In what way are the results incorrect? We can't see what you see - it's a bit of a guessing game.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Sean Lange (5/1/2013)


    So what exactly is the issue here?

    I would suggest you use the newer join constructs. With this query you don't even need a where clause.

    Here is the syntax:

    from batt_State s

    join pnLU as p on s.PartNo = p.PartNo

    group by s.partNo, p.descript

    I'd change that to:

    SELECT s.partNo, p.descript,

    CLNT= SUM(CASE WHEN battState = '99' THEN qty ELSE 0 END),

    AVAIL= SUM(CASE WHEN battState = '1' THEN qty ELSE 0 END),

    QAH= SUM(CASE WHEN battState = '20' THEN qty ELSE 0 END),

    BOOST= SUM(CASE WHEN battState = '30' THEN qty ELSE 0 END),

    RESTING = SUM(CASE WHEN battState = '31' THEN qty ELSE 0 END)

    FROM batt_State s

    INNER JOIN pnLU p

    ON s.PartNo = p.PartNo

    WHERE battState IN ('1','20','30','31','99')

    GROUP BY s.partNo, p.descript

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Got it! Thanks. I found the issue, it was in my test value. My code reply is correct.

  • ChrisM@Work (5/1/2013)


    Sean Lange (5/1/2013)


    So what exactly is the issue here?

    I would suggest you use the newer join constructs. With this query you don't even need a where clause.

    Here is the syntax:

    from batt_State s

    join pnLU as p on s.PartNo = p.PartNo

    group by s.partNo, p.descript

    I'd change that to:

    SELECT s.partNo, p.descript,

    CLNT= SUM(CASE WHEN battState = '99' THEN qty ELSE 0 END),

    AVAIL= SUM(CASE WHEN battState = '1' THEN qty ELSE 0 END),

    QAH= SUM(CASE WHEN battState = '20' THEN qty ELSE 0 END),

    BOOST= SUM(CASE WHEN battState = '30' THEN qty ELSE 0 END),

    RESTING = SUM(CASE WHEN battState = '31' THEN qty ELSE 0 END)

    FROM batt_State s

    INNER JOIN pnLU p

    ON s.PartNo = p.PartNo

    WHERE battState IN ('1','20','30','31','99')

    GROUP BY s.partNo, p.descript

    Oh sure...that will only be better if you want the query to be faster. 😀

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (5/1/2013)


    ChrisM@Work (5/1/2013)


    Sean Lange (5/1/2013)


    So what exactly is the issue here?

    I would suggest you use the newer join constructs. With this query you don't even need a where clause.

    Here is the syntax:

    from batt_State s

    join pnLU as p on s.PartNo = p.PartNo

    group by s.partNo, p.descript

    I'd change that to:

    SELECT s.partNo, p.descript,

    CLNT= SUM(CASE WHEN battState = '99' THEN qty ELSE 0 END),

    AVAIL= SUM(CASE WHEN battState = '1' THEN qty ELSE 0 END),

    QAH= SUM(CASE WHEN battState = '20' THEN qty ELSE 0 END),

    BOOST= SUM(CASE WHEN battState = '30' THEN qty ELSE 0 END),

    RESTING = SUM(CASE WHEN battState = '31' THEN qty ELSE 0 END)

    FROM batt_State s

    INNER JOIN pnLU p

    ON s.PartNo = p.PartNo

    WHERE battState IN ('1','20','30','31','99')

    GROUP BY s.partNo, p.descript

    Oh sure...that will only be better if you want the query to be faster. 😀

    Yeah I know - cheap hey!!

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • newQty is in the same table. The application will update the value.

  • Sean Lange (5/1/2013)


    ChrisM@Work (5/1/2013)


    Sean Lange (5/1/2013)


    So what exactly is the issue here?

    I would suggest you use the newer join constructs. With this query you don't even need a where clause.

    Here is the syntax:

    from batt_State s

    join pnLU as p on s.PartNo = p.PartNo

    group by s.partNo, p.descript

    I'd change that to:

    SELECT s.partNo, p.descript,

    CLNT= SUM(CASE WHEN battState = '99' THEN qty ELSE 0 END),

    AVAIL= SUM(CASE WHEN battState = '1' THEN qty ELSE 0 END),

    QAH= SUM(CASE WHEN battState = '20' THEN qty ELSE 0 END),

    BOOST= SUM(CASE WHEN battState = '30' THEN qty ELSE 0 END),

    RESTING = SUM(CASE WHEN battState = '31' THEN qty ELSE 0 END)

    FROM batt_State s

    INNER JOIN pnLU p

    ON s.PartNo = p.PartNo

    WHERE battState IN ('1','20','30','31','99')

    GROUP BY s.partNo, p.descript

    Oh sure...that will only be better if you want the query to be faster. 😀

    That's also assuming that the business requirement is to return rows only where battState IN ('1','20','30','31','99'), which isn't stated by the OP. If he wants a row for every partNo in the batt_State table, but only wants the aggregate SUM values where battState IN ('1','20','30','31','99') (leaving 0 in these columns for other values of battState), then the WHERE battState IN ('1','20','30','31','99') clause should be omitted.

    Jason Wolfkill

  • wolfkillj (5/2/2013)


    Sean Lange (5/1/2013)


    ChrisM@Work (5/1/2013)


    Sean Lange (5/1/2013)


    So what exactly is the issue here?

    I would suggest you use the newer join constructs. With this query you don't even need a where clause.

    Here is the syntax:

    from batt_State s

    join pnLU as p on s.PartNo = p.PartNo

    group by s.partNo, p.descript

    I'd change that to:

    SELECT s.partNo, p.descript,

    CLNT= SUM(CASE WHEN battState = '99' THEN qty ELSE 0 END),

    AVAIL= SUM(CASE WHEN battState = '1' THEN qty ELSE 0 END),

    QAH= SUM(CASE WHEN battState = '20' THEN qty ELSE 0 END),

    BOOST= SUM(CASE WHEN battState = '30' THEN qty ELSE 0 END),

    RESTING = SUM(CASE WHEN battState = '31' THEN qty ELSE 0 END)

    FROM batt_State s

    INNER JOIN pnLU p

    ON s.PartNo = p.PartNo

    WHERE battState IN ('1','20','30','31','99')

    GROUP BY s.partNo, p.descript

    Oh sure...that will only be better if you want the query to be faster. 😀

    That's also assuming that the business requirement is to return rows only where battState IN ('1','20','30','31','99'), which isn't stated by the OP. If he wants a row for every partNo in the batt_State table, but only wants the aggregate SUM values where battState IN ('1','20','30','31','99') (leaving 0 in these columns for other values of battState), then the WHERE battState IN ('1','20','30','31','99') clause should be omitted.

    You're absolutely right, Wolfie - but as an experienced dev, I'd bet that you'd write the same as I did, or possibly both, and offer it to a stakeholder to choose.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (5/3/2013)


    wolfkillj (5/2/2013)


    Sean Lange (5/1/2013)


    ChrisM@Work (5/1/2013)


    Sean Lange (5/1/2013)


    So what exactly is the issue here?

    I would suggest you use the newer join constructs. With this query you don't even need a where clause.

    Here is the syntax:

    from batt_State s

    join pnLU as p on s.PartNo = p.PartNo

    group by s.partNo, p.descript

    I'd change that to:

    SELECT s.partNo, p.descript,

    CLNT= SUM(CASE WHEN battState = '99' THEN qty ELSE 0 END),

    AVAIL= SUM(CASE WHEN battState = '1' THEN qty ELSE 0 END),

    QAH= SUM(CASE WHEN battState = '20' THEN qty ELSE 0 END),

    BOOST= SUM(CASE WHEN battState = '30' THEN qty ELSE 0 END),

    RESTING = SUM(CASE WHEN battState = '31' THEN qty ELSE 0 END)

    FROM batt_State s

    INNER JOIN pnLU p

    ON s.PartNo = p.PartNo

    WHERE battState IN ('1','20','30','31','99')

    GROUP BY s.partNo, p.descript

    Oh sure...that will only be better if you want the query to be faster. 😀

    That's also assuming that the business requirement is to return rows only where battState IN ('1','20','30','31','99'), which isn't stated by the OP. If he wants a row for every partNo in the batt_State table, but only wants the aggregate SUM values where battState IN ('1','20','30','31','99') (leaving 0 in these columns for other values of battState), then the WHERE battState IN ('1','20','30','31','99') clause should be omitted.

    You're absolutely right, Wolfie - but as an experienced dev, I'd bet that you'd write the same as I did, or possibly both, and offer it to a stakeholder to choose.

    For something this short, I probably would just write it and show the stakeholder the results with and without the WHERE clause. Anything more complex, I'd want clarification of the requirements first - I don't like to spend too much time coding after phantom requirements. :hehe:

    Jason Wolfkill

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply