question using count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

question using count

Author

Topic

-Dman100-
Posting Yak Master

210 Posts

Posted - 2005-08-13 : 13:12:26

Is is possible to combine the following two select statements into one statement?

SELECT COUNT (ELIST_EMAIL_ADDR) AS intTotal
FROM dbo.ELIST

&

SELECT ELIST_EMAIL_ADDR, ELIST_DATE
FROM dbo.ELIST
ORDER BY ELIST_EMAIL_ADDR ASC

I'm new to using COUNT
I tried:

SELECT COUNT (ELIST_EMAIL_ADDR) AS intTotal, ELIST_EMAIL_ADDR, ELIST_DATE
FROM dbo.ELIST
ORDER BY ELIST_EMAIL_ADDR ASC

Thanks for any help.
Regards,
-D-

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-13 : 14:11:04

You can use a subquery to do that.

SELECT ...., (SELECT COUNT(...) FROM ...) AS Total
FROM ...
ORDER BY ...

Or derived table
SELECT ...., t.Total
FROM ...., (SELECT COUNT(...) AS Total FROM ...) AS t
ORDER BY ...

Or declare a variable
DECLARE @t INT
SET @t = (SELECT COUNT(...) FROM ...)
SELECT ..., @t AS Total
FROM ...
ORDER BY ...

Or...

rockmoose

-Dman100-
Posting Yak Master

210 Posts

Posted - 2005-08-13 : 15:15:19

Thanks Rockmoose,

I appreciate the help. Exactly what I needed.

Regards,
-D-

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-13 : 19:53:43

You're welcome

rockmoose

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-16 : 01:25:26

quote:
Originally posted by -Dman100-

Thanks Rockmoose,

I appreciate the help. Exactly what I needed.

Regards,
-D-

No doubt. rockmoose is always helpful in T-sql

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-16 : 03:56:40

Be aware that:

quote:

SELECT COUNT (ELIST_EMAIL_ADDR) AS intTotal
FROM dbo.ELIST

is equivalent to:

Select count(*) as intTotal
from dbo.EList
where elist_email_addr is not null

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-16 : 06:24:27

quote:
Originally posted by mmarovic

Be aware that:
quote:
SELECT COUNT (ELIST_EMAIL_ADDR) AS intTotal
FROM dbo.ELIST
is equivalent to:
Select count(*) as intTotal
from dbo.EList
where elist_email_addr is not null

Be aware that NULLS should be avoided

Cheers Madhivanan!

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources