[GA_KEN]

Hi all,

I have the following:

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

[osy45]

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.

[GA_KEN]

Can you provide a small example to make it a little clearer?

Thanks for the reply!

Ken

[andrewst]

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
);