Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163

    Unanswered: Default a sum into the else

    I find myself coding the following quite often:

    Code:
    SELECT M.District,
       SUM(case when P.PropType = 'T' or M.Status = 'SA' then 
                     V.CurrentTaxable else 0 end), 
       SUM(case when SUBSTRING(P.Code,1,1) = 'P' then 
                     V.CurrentTaxable else 0 end),
       SUM(case when P.PropType <> 'T' and M.Status <> 'SA' and SUBSTRING(P.Code,1,1) <> 0 then 
                     V.CurrentTaxable else 0 end)
      FROM TRValue V
     INNER JOIN TRProp P on P.Code = V.Code
     INNER JOIN TRMaster M on M.Year = V.Year and M.Parcel = V.Parcel
     WHERE V.Year = 2012 and M.Deleted = 0 and M.Status in ('OK', 'SA')
     GROUP BY M.District
     ORDER BY M.District
    In other words, I am trying to sum 1 variable into 3 categories depending upon certain conditions. I have to explicitly negate all the conditions from the first 2 SUM's to simulate a default ending else condition. Is there any construct in SQL that will me to do the last SUM if the first 2 SUM's fail without all the negation conditions?

    Thanks.

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT District
         , sum_1
         , sum_2
         , sum_all - sum_1 - sum_2 AS sum_3
      FROM ( SELECT M.District
                  , SUM(CASE WHEN P.PropType = 'T' or M.Status = 'SA' 
                             THEN V.CurrentTaxable 
                             ELSE 0 END) AS sum_1
                  , SUM(CASE WHEN SUBSTRING(P.Code,1,1) = 'P' 
                             THEN V.CurrentTaxable 
                             ELSE 0 END) AS sum_2
                  , SUM(V.CurrentTaxable) AS sum_all
               FROM ...
             GROUP
                 BY M.District ) AS t
    ORDER
        BY District
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Excellent! Works perfectly. Now I have to go into my code and re-examine all my other multiple sum queries.

    Thanks!

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Just my curiosity!

    Are conditions for sum 1 and sum 2 exclusive?
    In other words,
    are there any row which satisfy both of "P.PropType = 'T' or M.Status = 'SA'" and "SUBSTRING(P.Code,1,1) = 'P'"?

  5. #5
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    I made an error when formatting the code for the original question. I omitted part of the conditional on the second SUM. It should have read:

    Code:
       , SUM(CASE WHEN P.PropType = 'T' or M.Status = 'SA' 
                             THEN V.CurrentTaxable 
                             ELSE 0 END) AS sum_1
       , SUM(CASE WHEN SUBSTRING(P.Code,1,1) = 'P' and P.PropType <> 'T'
                             THEN V.CurrentTaxable 
                             ELSE 0 END) AS sum_2
       , SUM(V.CurrentTaxable) AS sum_all
    So, yeah, the conditions become exclusive.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What if a resulting row had P.PropType <> 'T', SUBSTRING(P.Code,1,1) = 'P' and M.Status = 'SA'?

    I thought that the row would be counted in both of sum_1 and sum_2.

  7. #7
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    In theory you are correct. In actuality that particular combination can never happen. The values that this code is summing up comes from a State Agency, thus the m.status of 'SA', meaning State Assessed.
    They are given only a handful of codes to work with and as such they are all coded with a certain type that disallows the combination you mentioned.

  8. #8
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Quote Originally Posted by LinksUp View Post
    In actuality that particular combination can never happen .. They are given only a handful of codes to work with and as such they are all coded with a certain type that disallows the combination you mentioned.
    Look up Logic Gem or some other decision table tools. These things are a sort of spread sheet for logic. You fill out a grid of conditions and actions,opuch a button and see if
    There are missing combinations of conditions.
    There contradictions.
    There are redudancies in the logic.

    Once the logic is proven correct, you push another button and generate if-then-else code in various programming languages. Logic Gem uses an estimate of the frequency of each action to optimize the generated code.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Celko View Post
    ... or some other decision table tools. These things are a sort of spread sheet for logic.
    You fill out a grid of conditions and actions, ...
    There are missing combinations of conditions.
    There contradictions.
    There are redudancies in the logic.

    ...
    Even if you had not those tools, decision table may be usefull to clarify what you want to do.

    Here is an example...
    (I might misunderstood something, but you might saw utilliy values in it.)
    Code:
    ----------------------+---------------------------------+---------------------------------+
    M.Status              | = 'SA'                          | <> 'SA'                         |
    ----------------------+----------------+----------------+----------------+----------------+
    P.PropType            | = 'T'          | <> 'T'         | = 'T'          | <> 'T'         |
    ----------------------+-------+--------+-------+--------+-------+--------+-------+--------+
    SUBSTRING(P.Code,1,1) | = 'P' | <> 'P' | = 'P' | <> 'P' | = 'P' | <> 'P' | = 'P' | <> 'P' |
    ======================+=======+========+=======+========+=======+========+=======+========+
    Possible combinaion   |   o   |   o    |   x   |   o    |   o   |   o    |   o   |   o    |
    ----------------------+-------+--------+-------+--------+-------+--------+-------+--------+
    sum_1                 |   o   |   o    |   -   |   o    |   o   |   o    |       |        |  P.PropType = 'T' or M.Status = 'SA'
    sum_2                 |       |        |   -   |        |       |        |   o   |        |  SUBSTRING(P.Code,1,1) = 'P' and P.PropType <> 'T'
    sum_3                 |       |        |       |        |       |        |       |   o    |
    ----------------------+-------+--------+-------+--------+-------+--------+-------+--------+
    
    o : Yes
    x : No
    - : Don't care

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •