calculating the number of rows displayed

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

calculating the number of rows displayed

Author

Topic

jonekim
Starting Member

35 Posts

Posted - 2011-06-28 : 14:56:59

I am creating a database for bank Registration System. I have to calculate the number of males and females users.

<<<<<<sample Database "Bank_Registration">>>>>>>>>>>>
1. Name char(30),

2. Sex int, #1 for male & 0 for female

3. Role char(4),

4. Address char(30),

5. Branch char(30)

<<<<stored procedure for displaying females and males>>>>>

CREATE PROCEDURE DisplayGender
@Sex int,
@Role char(4)
AS
BEGIN

SELECT * FROM Bank_Registration
WHERE (Sex = @Sex)
AND (Role = @Role)

END
GO
********************************************************
Now I want to calculate the number of rows displayed by this stored procedures. How to write stored procedure to display the number of rows displayed?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-28 : 15:08:09

SELECT count(*)
FROM Bank_Registration
WHERE (Sex = @Sex)
AND (Role = @Role)

The SP doesn't match your text. You say you want the number of male and female uses but are passing the sex and role to the SP.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jonekim
Starting Member

35 Posts

Posted - 2011-06-28 : 22:14:57

How to combine the two queries: I tried to combine them using UNION but got error;
*************************************************
CREATE PROCEDURE DisplayGender
@Sex int,
@Role char(4)
AS
BEGIN

SELECT * FROM Bank_Registration
WHERE (Sex = @Sex)
AND (Role = @Role)

UNION

SELECT count(*)
FROM Bank_Registration
WHERE (Sex = @Sex)
AND (Role = @Role)
END
GO
**************************************
But got the error:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

How to use UNION??

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-29 : 02:33:56

First you should explain what you want.
table structure -> example data -> the wanted result in relation to the sample data.
Then we are able to give a solution.

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-29 : 07:25:26

>> How to use UNION??
Why do you want to use a union?

I suspect you might want something like this but difficult to say unless you are willing to tell us what the objective is.

select
role ,
malecount = SUM(case when sex = 'male' then 1 else 0 end) ,
femalecount = SUM(case when sex = 'female' then 1 else 0 end)
from tbl
group by role

or maybe

select
role ,
sex
count(*)
from tbl
group by role, sex

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-06-29 : 07:59:33

I thought the key to this was in the question. "number of rows DISPLAYED"
Display = front end formatting/calculating.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-29 : 08:19:41

Probably be wasteful to return the full resultset to the front end when it only needs a page of rows.

Oops - wrong thread
For this one would be wasteful to return the rows if you only want a count - the key is what the display needs to be which still isn't clear.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-06-29 : 08:51:09

i agree. i read it as a count of what was being displayed, which you could calculate on the fly in your report / web page.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources