Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    126

    Question Unanswered: Combined into one select?

    Hi all,

    I have the following:

    Code:
    select  count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1
    select  count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1
    select  count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1
    select  count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1
    select  count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1
    select  count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1
    Is there anyway to combined them into one query? So I get one result?

    Thanks,

    Ken
    Last edited by GA_KEN; 03-17-03 at 11:26.

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    try the case construct

    Syntax case
    when search_condition then expression
    [when search_condition then expression]...
    [else expression]
    end
    case and values syntax:
    case expression
    when expression then expression
    [when expression then expression]...
    [else expression]
    end
    Parameters case
    begins the case expression.
    when
    precedes the search condition or the expression to be compared.

  3. #3
    Join Date
    Jan 2003
    Posts
    126
    Can you provide a small example to make it a little clearer?

    Thanks for the reply!

    Ken

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by GA_KEN
    Can you provide a small example to make it a little clearer?

    Thanks for the reply!

    Ken
    I'd have prefered to start with all the data in one table, but this will do it (if I haven't made any mistakes):

    select sum(UNDER25_12_SINGLE) AS UNDER25_12_SINGLE
    , sum(UNDER25_12_MLTIPLE) AS UNDER25_12_MULTIPLE
    , sum(UNDER25_36_SINGLE) AS UNDER25_36_SINGLE
    , sum(UNDER25_36_MLTIPLE) AS UNDER25_36_MULTIPLE
    , sum(UNDER25_60_SINGLE) AS UNDER25_60_SINGLE
    , sum(UNDER25_60_MLTIPLE) AS UNDER25_60_MULTIPLE
    from
    (
    select sum( case when AccountCount = 1 then 1 else 0 end) AS UNDER25_12_SINGLE
    , sum( case when AccountCount > 1 then 1 else 0 end) AS UNDER25_12_MULTIPLE
    , 0 AS UNDER25_36_SINGLE
    , 0 AS UNDER25_36_MULTIPLE
    , 0 AS UNDER25_60_SINGLE
    , 0 AS UNDER25_60_MULTIPLE
    from OH_UNDER25_12MONTHS
    UNION ALL
    select 0 AS UNDER25_12_SINGLE
    , 0 AS UNDER25_12_MULTIPLE
    , sum( case when AccountCount = 1 then 1 else 0 end) AS UNDER25_36_SINGLE
    , sum( case when AccountCount = 1 then 1 else 0 end) AS UNDER25_36_MULTIPLE
    , 0 AS UNDER25_60_SINGLE
    , 0 AS UNDER25_60_MULTIPLE
    from OH_UNDER25_36MONTHS
    UNION ALL
    select 0 AS UNDER25_12_SINGLE
    , 0 AS UNDER25_12_MULTIPLE
    , 0 AS UNDER25_36_SINGLE
    , 0 AS UNDER25_36_MULTIPLE
    , sum( case when AccountCount = 1 then 1 else 0 end) AS UNDER25_60_SINGLE
    , sum( case when AccountCount = 1 then 1 else 0 end) AS UNDER25_60_MULTIPLE
    from OH_UNDER25_36MONTHS
    );

Posting Permissions

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