SUM(Case) in SQL

While answering some questions in the forums, I notice that there are a lot questions that have queries that could be quickly modified to make them more efficient. Queries like:

SELECT (SELECT SUM(Column1) FROM MyTable WHERE Column2 = 'Some Value') as FieldNeeded1,

(SELECT SUM(Column1) FROM MyTable WHERE Column2 = 'Some Other Value') as FieldNeeded2,

(SELECT SUM(Column1) FROM MyTable WHERE Column2 = 'Some Other Value 3') as FieldNeeded3, ETC

 

Can be easily rewritten as :

SELECT SUM(CASE WHEN Column2 = 'Some Value' THEN Column1 ELSE 0 END) as FieldNeeded1,

SUM(CASE WHEN Column2 = 'Some Other Value' THEN Column1 ELSE 0 END) as FieldNeeded2,

SUM(CASE WHEN Column2 = 'Some Other Value 3' THEN Column1 ELSE 0 END) as FieldNeeded3

FROM MyTable

 

While it might not save much on a small table, think when the there are a million records. Running a select 3 times to pull the same fields would be very inefficient. However, this method is very efficient because the select only has to happen once, and the data is then just summarized as it is needed.

Anybody else have some very simple tips to increase peoples query performance?

17 Comments

  • Legal serviu muito bem para mim, faça um exemplo para converter um varchar dentro do case pois eu não sei como colocar valeu

  • So why can't I:

    SELECT SUM(CASE WHEN rm40401.docabrev = 'CR' THEN - rm20101.CURTRXAM ELSE (CASE WHEN rm40401.docabrev = 'PMT' THEN - rm20101.CURTRXAM ELSE
    (CASE WHEN rm40401.docabrev = 'RTN' THEN - rm20101.CURTRXAM ELSE rm20101.CURTRXAM END) END) END) AS AMOUNT,
    RM20101.CUSTNMBR, RM00101.CUSTNAME, RM40401.DOCABREV
    FROM RM20101 INNER JOIN
    RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN
    RM40401 ON RM20101.RMDTYPAL = RM40401.RMDTYPAL

  • I think you're missing a part of how case works... it's a case structure used specifically so you don't have to nest conditional logic (if statements, et all). Your query rewritten like below should work (except for anything I fat-finger):

    SELECT SUM(CASE WHEN rm40401.docabrev = 'CR' THEN -rm20101.CURTRXAM
    WHEN rm40401.docabrev = 'PMT' THEN -rm20101.CURTRXAM
    WHEN rm40401.docabrev = 'RTN' THEN -rm20101.CURTRXAM
    ELSE rm20101.CURTRXAM END)
    AS AMOUNT,
    RM20101.CUSTNMBR, RM00101.CUSTNAME, RM40401.DOCABREV
    FROM RM20101 INNER JOIN
    RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN
    RM40401 ON RM20101.RMDTYPAL = RM40401.RMDTYPAL

    Or even more concisely

    SELECT SUM(CASE WHEN rm40401.docabrev in ('CR','PMT','RTN') THEN -rm20101.CURTRXAM
    ELSE rm20101.CURTRXAM END)
    AS AMOUNT,
    RM20101.CUSTNMBR, RM00101.CUSTNAME, RM40401.DOCABREV
    FROM RM20101 INNER JOIN
    RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN
    RM40401 ON RM20101.RMDTYPAL = RM40401.RMDTYPAL

  • Great explanation and examples for a novice like me.
    Thanks.

  • Now, can we sum the three sums (FieldNeeded1,2,3) in to a separate column? and how?

    Thnx in advance

  • Watch out,
    if you have the proper index on Column2, the first 3 queries will only work on a subset of the table, while the query with the case construct will result in a full table scan.
    The result can be millisecond to run the first queries versus seconds (or more) to run the second one

  • Its an amazing post,Really helpd me a lot,thnx to the authors kudos

  • Excellent Post, Thank You.

  • Thanks dear,

    It worked very fine for me.

  • Absolutely wonderful....excelent examples!

  • Thanks for posting - just what i was looking for

  • Thanks to Rick, good example.

  • gracias me salvaste la vida
    thanks you saved my life

  • This pattern fails the edge-case of no records. You'd expect a count of zero, but instead get nulls. You can either wrap them in a subselect with a COALESCE, or instead do use

    COUNT(NULLIF(Column2, 'Some Value')) AS FieldNeeded1

  • Thanks a lot, I was trying different methods. I found some which are more complex so far this is the easiest for me.

  • can you help me with this?
    "SELECT DISTINCT * FROM tbl_Invoice,tbl_OR WHERE tbl_invoice.invoicenum = "01"' and tbl_OR.amount = (SELECT SUM(tbl_OR.amount) as total from tbl_OR)"

  • Saved my time.. great..

Comments have been disabled for this content.