Using count(*) more than once?

Question

Post reply

Using count(*) more than once?

Dan Thurman-251670

Grasshopper

Points: 16
More actions
January 3, 2007 at 9:46 am

#122211

Is it possible to use the count(*) statement more than once such as:
SELECT members, count(*) AS memberCnt, count(*) AS nonMemberCnt

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 738693

January 3, 2007 at 11:38 am

#681008

Yes, use the column name instead of the *

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Yes, but why? Both of those will give the same value because you are counting all rows (COUNT(*)) both times.

And also, what are you trying to do?

Providing the table structure, sample data, and what results you want will help us come up with a solution.

-SQLBill

Lowell SSC Guru Points: 323547 More actions · Answer 3

soemthing like this is probably what you want: you want to use a case statement to determine how a row gets counted

SELECT SUM(CASE WHEN DUES > 0 THEN 1 ELSE 0 END) AS MEMBERCOUNT,

SUM(CASE WHEN DUES > 0 THEN 0 ELSE 1 END) AS NONMEMBERCOUNT

FROM SOMETABLE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sergiy SSC Guru Points: 110209 More actions · Answer 4

It must be rather something like this:

SELECT count(P.ID) as Persons,

count(M.ID) as Members,

count(P.ID) - count(M.ID) as NonMembers

FROM dbo.Person P

LEFT JOIN dbo.Membership M ON M.PersonID = P.ID

_____________
Code for TallyGenerator

Dan Thurman-251670 Grasshopper Points: 16 More actions · Answer 5

Thanks to all who have responded!

I have used chosen to use Lowell's suggestion and it was what I was looking for. What I needed was a way to count up different levels of memberships such as a member having a full support (home page, pictures, the whole encillada), a member having partial support (no home page, but has pictures for example) and then there are other members with lesser benefits and lastly persons with no membership at all and is using free services. I needed that info so that I can display results accordingly.

Again, thanks to you all!

Dan