Sql count help - 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

Sql count help

Author

Topic

phoenix23
Starting Member

6 Posts

Posted - 2010-07-14 : 04:30:24

hi everyone, right now i am doing an assignment in asp.net.
my current table MemberManagement is like this

MemberManagement
strFullName.......strEmail............blnIsPosted
Amresh............amresh@hotmail.com......0
Amresh............amresh@hotmail.com......1
Amresh............amresh@hotmail.com......1
Izzat.............izzat@hotmail.com.......1
Izzat.............izzat@hotmail.com.......1

But right now, I need to get this

MemberManagement
strFullName.......strEmail................blnIsPosted
Amresh..........Amresh@hotmail.com...........2
Izzat...........Izzat@hotmail.com............2

basically the blnIsPosted is to count the number of dvd titles which is posted to the member

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-14 : 04:34:23

Select strFullName, strEmail, sum(blnIsPosted) from table_name
group by strFullName, strEmail

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-07-14 : 04:36:42

quote:
Originally posted by phoenix23

hi everyone, right now i am doing an assignment in asp.net.
my current table MemberManagement is like this

MemberManagement
strFullName.......strEmail............blnIsPosted
Amresh............amresh@hotmail.com......0
Amresh............amresh@hotmail.com......1
Amresh............amresh@hotmail.com......1
Izzat.............izzat@hotmail.com.......1
Izzat.............izzat@hotmail.com.......1

But right now, I need to get this

MemberManagement
strFullName.......strEmail................blnIsPosted
Amresh..........Amresh@hotmail.com...........2
Izzat...........Izzat@hotmail.com............2

basically the blnIsPosted is to count the number of dvd titles which is posted to the member

do u want the count or sum of the blnisposted.... ur showing the sum in the required output.

phoenix23
Starting Member

6 Posts

Posted - 2010-07-14 : 04:41:15

hi... sorry its actually the sum of all "true" blnIsPosted
so lets say its like

strFullName.......strEmail............blnIsPosted
Amresh............amresh@hotmail.com......0
Amresh............amresh@hotmail.com......1
Amresh............amresh@hotmail.com......1
Amresh............amresh@hotmail.com......1
Izzat.............izzat@hotmail.com.......1
Izzat.............izzat@hotmail.com.......1

MemberManagement
strFullName.......strEmail................blnIsPosted
Amresh..........Amresh@hotmail.com...........3
Izzat...........Izzat@hotmail.com............2

blnIsPosted is a boolean variable.. i dont know why, my teacher set it as such

thanks for your help guys

phoenix23
Starting Member

6 Posts

Posted - 2010-07-14 : 04:50:56

YAY i got it :D !!!
I used senthil_nagore code and modify it a little from browsing the net... basically the code i got look kinda like this

Select strFullNameUs, strEmailus, sum(case when blnIsPostedMO = 1 then 1 else 0 end) from membermanagement
group by strFullNameus, strEmailus

thanks alot guys!! :D

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-14 : 04:56:21

quote:
Originally posted by phoenix23

YAY i got it :D !!!
I used senthil_nagore code and modify it a little from browsing the net... basically the code i got look kinda like this

Select strFullNameUs, strEmailus, sum(case when blnIsPostedMO = 1 then 1 else 0 end) from membermanagement
group by strFullNameus, strEmailus

thanks alot guys!! :D

No need to modify..

If we add with "0" that doesn't change the value.

Still u want to change use Where clause

Select strFullNameUs, strEmailus, sum(blnIsPostedMO) from membermanagement where blnIsPostedMO=1
group by strFullNameus, strEmailus

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

phoenix23
Starting Member

6 Posts

Posted - 2010-07-14 : 10:48:18

cheers mate
but there's one prob

when i put it on asp.net,
it returns
strFullName.......strEmail................blnIsPosted
Amresh..........Amresh@hotmail.com...........True
Izzat...........Izzat@hotmail.com............True

But on sql it returns whats i want which is
strFullName.......strEmail................blnIsPosted
Amresh..........Amresh@hotmail.com...........3
Izzat...........Izzat@hotmail.com............2

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-14 : 10:53:11

Is the ASP field that is displaying coded as a boolean ? If so, you'll need to change the type of that to int...

If I remember correctly, it treats any non-zero as true.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources