Combining SQL string for counting males/females

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

Combining SQL string for counting males/females

Author

Topic

oap
Yak Posting Veteran

60 Posts

Posted - 2012-02-21 : 13:55:45

Hi...

I've a database titled "users". In it are a list of my users, their passwords, preferences, email addresses, etc.

example fields:

NAME: Ronnie W
GENDER: Female
PASSWORD: dkdjejw

NAME: Tom Graw
GENDER: Male
PASSWORD: dkjd3eo

I am writing a script to count the total number of users, and with that count also give me the total number of MALE and FEMALE users.

My code:

strSQL = "SELECT COUNT(*) as nusers from users"
set objRc = objCntv.Execute(strSQL)
totusr = objrc("nusers")
' totuser is total users from the users database

strSQL = "SELECT COUNT(gender) as totmale from users where gender = 'Male'"
set objRc = objCntv.Execute(strSQL)
totmale = objrc("totmale")
' totmale is the total males

strSQL = "SELECT COUNT(gender) as totfemale from users where gender = 'Female'"
set objRc = objCntv.Execute(strSQL)
totfemale = objrc("totfemale")
' totfemale is the total females

response.write "Users:" & totusr
response.write " Male:" & totmale
response.write " Female:" & totfemale

This works but it is poorly coded and I don't like using three connections in a row. I know I could add both male and female counts together to eliminate the first SQL string call (the total # of users overall).

Is there a one line of code that I could use to query the database to count the number of "Male" and "Female" users within the GENDER field?

While ASP is a strong point for me, SQL is a weak point and I sometimes have to ask around for help :)

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 14:00:11

[code]SELECT COUNT(*) as users,COUNT(case when gender='Male' then 1 end) as totmale,COUNT(case when gender='Female' then 1 end) as totfemale from users
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-21 : 14:06:24

i would use sum(case...) instead of count(case...)

No, you're never too old to Yak'n'Roll if you're too young to die.

oap
Yak Posting Veteran

60 Posts

Posted - 2012-02-21 : 14:46:07

Worked like a charm. Thank you so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 14:46:52

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2012-02-21 : 16:29:46

Actually before you send me the bill... I have one more query :)

I have a table named 'sites' that contains a list of places to visit.

I need to count the total number of places (as TOTLOC or "total locations"). This is a basic row count.

I also need to count the number of cities in the table as held in a row named "CLOSEST" - the word "closest" was chosen as it represents the closest major city. I use the DISTINCT feature so that I don't end up with 20 results if 20 locations are located in "toronto", just one result.

finally I require a count for all locations in the "sites" dbase that have the field FULLMEMBER set to 'y' (in other words, this counts the number of places that you need to be a full member to view the location)

It's another situation of trying to merge the three strings into one. I've tried merging them based on the original responses given above but it fails.

strsql = "SELECT COUNT(DISTINCT closest) as ID from sites"
set objrc = objCntv.Execute(strSQL)
totrows = objrc("id")

strsql = "SELECT COUNT(ID) as ID from sites"
set objrc = objCntv.Execute(strSQL)
totloc = objrc("id")

strsql = "SELECT COUNT(id) as ID from sites where fullmember = 'y'"
set objrc = objCntv.Execute(strSQL)
fullm = objrc("id")

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 17:24:23

[code]
SELECT COUNT(DISTINCT closest) as ID1,COUNT(ID) as ID2,COUNT(case when fullmember = 'y' then id else null end) as ID3 from sites
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2012-02-21 : 17:27:41

Excellent! I owe you a beer if you're ever in Canada :)

oap
Yak Posting Veteran

60 Posts

Posted - 2012-02-21 : 17:47:42

Well maybe I can fit in one more question before calling it a night.

I'm trying to plot a bsr graph indicating how many new entries have been added to the database in the last week. To do this I need to count back for the last 7 days.

I'm having some issue though...

for lp = 1 to 7
strsql = "SELECT * from sites where crdate >= dateadd(day, -" & lp & ", getdate())"
set objrc = objCntv.Execute(strSQL)
while not objrc.EOF
response.write objrc("town")
objrc.movenext
wend
response.write " "
next

If I run this, it works...
day 1: Place1
day 2: Place1, Place 2
day 3: Place1, Place 2, This place
day 4: Place1, Place 2, This place, That place

The issue is I need the count for entries added per day BUT if I change the string from

strsql = "SELECT * from sites where crdate >= dateadd(day, -" & lp & ", getdate())"

to

strsql = "SELECT * from sites where crdate = dateadd(day, -" & lp & ", getdate())"

which should return all entries EQUAL to the date of (date minus lp) it gives me ALL records in the entire database.

not sure why I have to select > or < but can't use plain =

Also looping seems a messy way to do this... I am needing numerical values for 7 days counting only by date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 19:29:00

strsql = "SELECT dateadd(dd,datediff(dd,0,getdate()),0),count(*) as cnt from sites where crdate >= dateadd(day, -" & lp & ", getdate()) group by dateadd(dd,datediff(dd,0,getdate()),0)"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2012-02-21 : 19:59:29

Each GROUP BY expression must contain at least one column that is not an outer reference.

:(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-22 : 00:11:14

ah my bad. it should be


strsql = "SELECT dateadd(dd,datediff(dd,0,crdate ),0),count(*) as cnt from sites where crdate >= dateadd(day, -" & lp & ", getdate()) group by dateadd(dd,datediff(dd,0,crdate ),0)"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

oap
Yak Posting Veteran

60 Posts

Posted - 2012-02-22 : 07:33:22

You sir are amazing. It works perfectly and returns the number of places created per day.

When I first tried it, I was baffled as to how it worked when using the >= comparison though. I thought if I was increasing the
number of days back (0, -1, -2, -3) that dates would accumulate.

example:
Feb. 22, 2012 3 places created on this date
Feb. 21, 2012 1 place created this date
Feb. 20, 2012 34 places created this date

and by that logic, by the time I queried for -2 (Feb. 20, 2012) wouldn't it INCLUDE the totals from Feb. 21 and Feb. 22 since they are ">=" the date being used to query.

After some confusion I believe that this query is NOT supposed to be run 7 times for each day of the week but just ONCE using a value of 7 days and the GROUP BY handles the individual dates.

I just have to return the recordsets results... I guess this is more of a rhetoric question, since it works fantastic. I even took the time to see if your blog had a donate money option but it didn't.

strsql = "SELECT dateadd(dd,datediff(dd,0,crdate ),0),count(*) as cnt from sites where crdate >= dateadd(day, -7, getdate()) group by dateadd(dd,datediff(dd,0,crdate ),0)"
set objrc = objCntv.Execute(strSQL)

while not objrc.EOF
response.write objrc("cnt") & " "
objrc.movenext
wend

You've helped tremendously.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-22 : 09:38:19

quote:
Originally posted by oap

You sir are amazing. It works perfectly and returns the number of places created per day.

When I first tried it, I was baffled as to how it worked when using the >= comparison though. I thought if I was increasing the
number of days back (0, -1, -2, -3) that dates would accumulate.

example:
Feb. 22, 2012 3 places created on this date
Feb. 21, 2012 1 place created this date
Feb. 20, 2012 34 places created this date

and by that logic, by the time I queried for -2 (Feb. 20, 2012) wouldn't it INCLUDE the totals from Feb. 21 and Feb. 22 since they are ">=" the date being used to query.

After some confusion I believe that this query is NOT supposed to be run 7 times for each day of the week but just ONCE using a value of 7 days and the GROUP BY handles the individual dates.

I just have to return the recordsets results... I guess this is more of a rhetoric question, since it works fantastic. I even took the time to see if your blog had a donate money option but it didn't.

strsql = "SELECT dateadd(dd,datediff(dd,0,crdate ),0),count(*) as cnt from sites where crdate >= dateadd(day, -7, getdate()) group by dateadd(dd,datediff(dd,0,crdate ),0)"
set objrc = objCntv.Execute(strSQL)

while not objrc.EOF
response.write objrc("cnt") & " "
objrc.movenext
wend

You've helped tremendously.

you're welcome

Glad that I could be of help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources