Combined into one select

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,

KenPlace UNION between the select statements.

Originally posted by 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|||Union sort of worked, but I need the data in columns, union gave me rows.|||try this

select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) as UNDER25_12_SINGLE ,
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) as UNDER25_12_MULTIPLE
:
:|||Try this:

select * from
(select count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1) as A,
(select count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1) as B,
(select count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1) as C,
(select count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1) as D,
(select count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1) as E,
(select count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1) as F

Originally posted by GA_KEN
Union sort of worked, but I need the data in columns, union gave me rows.|||Originally posted by msieben
try this

select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) as UNDER25_12_SINGLE ,
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) as UNDER25_12_MULTIPLE
:
:

This works just like I wanted! I knew there had to be a way! I've been pulling my hair out trying to get it to work!|||You guys are awesome!!

Thanks for all your help!

Ken|||select
(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount = 1) AS UNDER25_12_SINGLE
,(select count([Account Number]) from OH_UNDER25_12MONTHS where AccountCount > 1) AS UNDER25_12_MULTIPLE
,(select count([Account Number]) from OH_UNDER25_36MONTHS where AccountCount = 1) AS UNDER25_36_SINGLE
,(select count([Account Number]) from OH_UNDER25_36MONTHS where AccountCount > 1) AS UNDER25_36_MULTIPLE
,(select count([Account Number]) from OH_UNDER25_60MONTHS where AccountCount = 1) AS UNDER25_60_SINGLE
,(select count([Account Number]) from OH_UNDER25_60MONTHS where AccountCount > 1) AS UNDER25_60_MULTIPLE

Good luck !

Combined Index not using in SQL 7.0 SP4

Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks Binu
The optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>
|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' -- by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu

Combined Index not using in SQL 7.0 SP4

Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?
Thanks BinuThe optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful...Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Binu Abraham" <abrahambinu@.verizon.net> wrote in message
news:OaKGlso1EHA.3236@.TK2MSFTNGP15.phx.gbl...
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index
not
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu
>|||Binu,
The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!
You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.
If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.
Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!
Hope this helps,
Gert-Jan
Binu Abraham wrote:
> Table -- Survey_invites
> Primary key Clustered index on (survey_id,email_id).
> Query 1
> select * from survey_invites where survey_id='003' -- by default Index no
t
> used ( need to give hint to make use of index)
> with hint it takes 1 sec v/s 3 min without hint !!!
> Query 2
> select * from survey_invites where survey_id='003' and email_id='nnn' --
by
> default Index used
> But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
> used.
> Is this a known problem in SQL 7.0 ? any help appreciated .?
> Thanks Binu