none
WHERE condition in SELECT Statement

    Domanda

  •  

    Hi All,

    I want get the result in a single SELECT statement

     

     SELECT

     SUM (PAID_LOSS),

    SUM (PAID_EXP)

    GROUP BY COMPANY

    FROM VIEW_POLICY

    WHERE  Accounting_Period  GE  200701  and LE 200712

    -************************************

    SELECT

     SUM (MEDICAL_RESERVE)

    GROUP BY COMPANY

    FROM VIEW_POLICY

    WHERE  Accounting_Period  LE 200712

     

     

    mercoledì 14 novembre 2007 17:14

Risposte

  • I just realized that the previous example did not properly account for your two differing WHERE conditions; perhaps something using CASE statements such as:

     

    Code Block

    declare @view_policy table
    ( company varchar(9),
      paid_loss decimal(9,2),
      paid_exp decimal(9,2),
      medical_reserve decimal(9,2),
      accounting_period integer
    )
    insert into @view_policy
    select 'A', 14.5, 16.2,  5.7, 200601 union all
    select 'A', 13.2, 21.6,  8.3, 200706 union all
    select 'B', 27.3, 28.8,  9.1, 200705 union all
    select 'C', 14.3, 17.4,  6.8, 200705

     

    SELECT company,
     SUM ( case when accounting_period >= 200701 then PAID_LOSS else 0 end) as paid_loss,
    SUM (case when accounting_period >= 200701 then PAID_EXP else 0 end) as paid_exp,
    sum(medical_reserve) as medical_reserve
    FROM @VIEW_POLICY
    WHERE Accounting_Period <= 200712
    GROUP BY COMPANY

     

    /* -------- Sample Output: --------
    company   paid_loss  paid_exp  medical_reserve
    --------- ---------- --------- ----------------
    A         13.20      21.60     14.00
    B         27.30      28.80     9.10
    C         14.30      17.40     6.80
    */

     

     

    mercoledì 14 novembre 2007 20:15
    Moderatore

Tutte le risposte

  • Join your two select statements with a UNION ALL clause; give a look to UNION ALL in books online.  Also, your GROUP BY clauses need to follow your WHERE clauses.  This WHERE CLAUSE

     

    Code Block
    WHERE  Accounting_Period  GE  200701  and LE 200712

     

     

    needs to be replaced by something like:

     

    Code Block

    WHERE  Accounting_Period  >=  200701  and  Accounting_Period <= 200712

     

     

    and in general where you are using a 'GE' operator use '>='; where you are using an 'LE' operator use '<='.

     

    The other question has to do with the issue of column or rows.  Are you wanting the information from the second query to be a column that is coordinated with the rest or are you wanting separate rows?

     

    Since the number of columns are not identical a simple UNON ALL might not be the answer.

     

    If you just want the second query as an additional column, something like this might work:

     

    Code Block

    declare @view_policy table
    ( company varchar(9),
      paid_loss decimal(9,2),
      paid_exp decimal(9,2),
      medical_reserve decimal(9,2),
      accounting_period integer
    )
    insert into @view_policy
    select 'A', 14.5, 16.2,  5.7, 200705 union all
    select 'A', 13.2, 21.6,  8.3, 200706 union all
    select 'B', 27.3, 28.8,  9.1, 200705 union all
    select 'C', 14.3, 17.4,  6.8, 200705

     

    SELECT company,
     SUM (PAID_LOSS) as paid_loss,
    SUM (PAID_EXP) as paid_exp,
    sum(medical_reserve) as medical_reserve
    FROM @VIEW_POLICY
    WHERE  Accounting_Period  >=  200701  and Accounting_Period <= 200712
    GROUP BY COMPANY

     

    /* -------- Sample Output: --------
    company   paid_loss  paid_exp  medical_reserve
    --------- ---------- --------- ----------------
    A         27.70      37.80     14.00
    B         27.30      28.80     9.10
    C         14.30      17.40     6.80
    */

     

     

    mercoledì 14 novembre 2007 17:17
    Moderatore
  • I just realized that the previous example did not properly account for your two differing WHERE conditions; perhaps something using CASE statements such as:

     

    Code Block

    declare @view_policy table
    ( company varchar(9),
      paid_loss decimal(9,2),
      paid_exp decimal(9,2),
      medical_reserve decimal(9,2),
      accounting_period integer
    )
    insert into @view_policy
    select 'A', 14.5, 16.2,  5.7, 200601 union all
    select 'A', 13.2, 21.6,  8.3, 200706 union all
    select 'B', 27.3, 28.8,  9.1, 200705 union all
    select 'C', 14.3, 17.4,  6.8, 200705

     

    SELECT company,
     SUM ( case when accounting_period >= 200701 then PAID_LOSS else 0 end) as paid_loss,
    SUM (case when accounting_period >= 200701 then PAID_EXP else 0 end) as paid_exp,
    sum(medical_reserve) as medical_reserve
    FROM @VIEW_POLICY
    WHERE Accounting_Period <= 200712
    GROUP BY COMPANY

     

    /* -------- Sample Output: --------
    company   paid_loss  paid_exp  medical_reserve
    --------- ---------- --------- ----------------
    A         13.20      21.60     14.00
    B         27.30      28.80     9.10
    C         14.30      17.40     6.80
    */

     

     

    mercoledì 14 novembre 2007 20:15
    Moderatore
  • when I use the UNION ALL it is showing the values as:

     

    2087900

    6758900

       but  I want to see them as

     

    2087900    6758900

    giovedì 15 novembre 2007 20:23
  • It's working fine now !!!

     

     Thanks a lot !!!

     

    giovedì 15 novembre 2007 20:32