Using COUNT in Stored Procedures

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

SQL Server 2000 Forums

SQL Server Development (2000)

Using COUNT in Stored Procedures

Author

Topic

ewade
Starting Member

36 Posts

Posted - 2002-05-10 : 15:31:38

I'm baaaaack with another newbie question. We're collecting data from machine-read surveys, and have a need to summarize the data we collect. I know how to do this on a field-by-field basis, but now I've been asked to create a page on our intranet that shows up-to-the-minute results. It seems to me that the most efficient way of doing this is to call a stored procedure from the web server.

With the exception of a single field (Zip code), all fields are constrained responses.

Is there a way to combine multiple select statements in a stored procedure and then return the results back to the web server? Doing these individually work out like this:

SELECT zip, count(zip) as 'No. of Responses' FROM dbo.Table Group By zip

SELECT Gender, count(Gender) as 'No. of Responses' FROM dbo.Table Group By Gender

SELECT Goal, count(Goal) as 'No. of Responses' FROM dbo.Table Group By Goal

Obviously, for each statement it will return a count for each unique value stored in the field. How, then, can I pass the results back to the web server?

Never stop learning!

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-05-10 : 15:42:11

One (of many) possible solution(s) is to use a union a union statement to create one resultset and populate your recordset with that.


SELECT zip, count(zip) as 'No. of Responses' FROM dbo.Table Group By zip
union
SELECT Gender, count(Gender) as 'No. of Responses' FROM dbo.Table Group By Gender
union
SELECT Goal, count(Goal) as 'No. of Responses' FROM dbo.Table Group By Goal

Justin

Edited by - justinbigelow on 05/10/2002 15:42:32

ewade
Starting Member

36 Posts

Posted - 2002-05-10 : 15:50:41

Thanks, but one of the problems (and I should have spelled this out) is that the data is of different types. To my limited knowledge, unions can only be used when the datatypes are identical between SELECT statements.

Never stop learning!

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-10 : 15:51:01

Mr.Bigs query may work if zip,gender and goal are all the same datatype. Whether or not you will be able to distinguish between them in the final resultset...idunno?

I would suggest you stay with three queries, thus three resultssets and look into the movenextresultset (or whatever) method . . .

EDIT: I am gonna give you the benefit of the doubt and assume 'Table' was a quick and dirty psuedo-code name, cause you would never use 'table' as an object name because it is a reserved word...right?...don't even use it here as example code, cause it'll cause ppl (like me

) to waste valuable keystrokes pointing it out....

<O>

Edited by - Page47 on 05/10/2002 15:55:02

ewade
Starting Member

36 Posts

Posted - 2002-05-10 : 15:59:16

LOL... yes, it's an alias. Quicker than typing out [2002AdultEducationStudentSurveyResults] three times.

Never stop learning!

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-05-10 : 16:00:26

I should have put a little more thought into my response

. I assumed the Zip and Gender would both be of char/varchar type, I realize Zip could be an int but since you don't perform math computations on a Zip char/varchar makes more sense. The Goal field sounds like an int and should therefore be cast as varchar to make sure all three resultsets have the same datatype in the first column. In fact if you were going to with the union then the gender should probably be cast to varchar in case it is stored as a single digit char (m/f) so that it will conform to the zip. Or you could just do what Page47 suggested

Justin

ewade
Starting Member

36 Posts

Posted - 2002-05-13 : 14:55:20

Thanks for the responses. Unfortunately, the data form used to collect responses from clients is already locked in stone, and contains a mixture of bit, varchar/char and int datatypes. It looks like my only recourse is going to be setting up a recordset for each of the thirty items that need to be summarized.

Never stop learning!

DrewBurlingame
Starting Member

49 Posts

Posted - 2002-05-13 : 17:44:23

You don't have to change the data coming into the database, as long as you change the data going out.

I think Justin has a great point in casting the field to the datatype you want. For example:

SELECT CAST(zip varchar), count(zip) as 'Num_of_Responses' FROM dbo.Table Group By zip
union
SELECT CAST(Gender varchar), , count(Gender) FROM dbo.Table Group By Gender
union
SELECT CAST(Goal varchar), , count(Goal) FROM dbo.Table Group By Goal

Wouldn't this allow you to create the union query, and thus return one recordset?

ewade
Starting Member

36 Posts

Posted - 2002-05-14 : 14:49:37

I tried your suggestions, but received a syntax error. Any additional suggestions are more than appreciated!

Here's a snippet of the query:

SELECT CAST(ZIP varchar), COUNT(ZIP) as 'zipcount' FROM [2002AdultEdSurveyData] GROUP BY ZIP
union
SELECT CAST(Gender varchar), COUNT(Gender) as 'gendercount' FROM [2002AdultEdSurveyData] GROUP BY Gender
union
SELECT CAST(MaritalStatus varchar), COUNT(MaritalStatus) as 'maritalcount' FROM [2002AdultEdSurveyData] GROUP BY MaritalStatus;

Never stop learning!

Edited by - ewade on 05/14/2002 14:52:31

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-14 : 14:54:06

SELECT CAST(ZIP as varchar), COUNT(ZIP) as 'zipcount' FROM [2002AdultEdSurveyData] GROUP BY CAST(ZIP as varchar)
union
SELECT CAST(Gender as varchar), COUNT(Gender) as 'gendercount' FROM [2002AdultEdSurveyData] GROUP BY CAST(Gender as varchar)
union
SELECT CAST(MaritalStatus as varchar), COUNT(MaritalStatus) as 'maritalcount' FROM [2002AdultEdSurveyData] GROUP BY CAST(MaritalStatus as varchar);

<O>

Edited by - Page47 on 05/14/2002 14:56:16

ewade
Starting Member

36 Posts

Posted - 2002-05-24 : 16:06:42

Thanks for the suggetions so far. I feel like I'm learning alot very rapidly from everyone. The problem with using UNION in this context is that I need to be able to discern results by column. I luck out in that all but one of my columns has constrained input. Here's what I am trying to accomplish (hopefully it makes sense):

CREATE PROCEDURE [StatCount]
(@pCode varchar(6))
As
begin
set @pCode=@pCode PLUS '%'
SELECT ZIP, COUNT(ZIP) as '#' FROM [2002AdultEdSurveyData] WHERE ProgramID LIKE @pCode GROUP BY ZIP;
SELECT Gender, COUNT(Gender) as '#' FROM [2002AdultEdSurveyData] WHERE ProgramID LIKE @pCode GROUP BY Gender;
SELECT MaritalStatus as 'Marital Status', COUNT(MaritalStatus) as '#' FROM [2002AdultEdSurveyData] WHERE ProgramID LIKE @pCode GROUP BY MaritalStatus;
SELECT Children as 'No. Children Under 18', COUNT(Children) as '#' FROM [2002AdultEdSurveyData] WHERE ProgramID LIKE @pCode GROUP BY Children;
SELECT Ethnicity, COUNT(Ethnicity) as '#' FROM [2002AdultEdSurveyData] WHERE ProgramID LIKE @pCode GROUP BY Ethnicity;
SELECT EmploymentStatus as 'Employment Status',COUNT(EmploymentStatus) as'#' FROM [2002AdultEdSurveyData] WHERE ProgramID LIKE @pCode GROUP BY EmploymentStatus;
SELECT EdLevel as 'Highest Completed Educational Level',COUNT(EdLevel) as '#' FROM [2002AdultEdSurveyData] WHERE ProgramID LIKE @pCode GROUP BY EdLevel;
end

Can I actually accomplish this with a single stored procedure? My ultimate goal (again) is to be able to create a 'query-on-the-fly' web page where a manager can type in a 2-digit program funding code and see raw counts for each value grouped by column.

P.S.- Before I get a spate of helpful tips because of it- I do know that ending the SELECT statements with the semicolon terminates the query process and I'll only get results from the first statement. I'm using them as a placeholder in this context. Also, I used PLUS instead of the actual symbol because for some odd reason it just won't display in the posting.

Never stop learning!

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-05-28 : 16:42:04

Just CROSS JOIN the lot of 'em.

setBasedIsTheTruepath
<O>

ewade
Starting Member

36 Posts

Posted - 2002-05-28 : 22:53:27

quote:

Just CROSS JOIN the lot of 'em.

setBasedIsTheTruepath
<O>

Okay, how do I do that?

Never stop learning!

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-05-29 : 11:18:15

Look it up in BOL.

Generally, your query would look like:

select a
cross join select b
cross join select c
....

which would produce a rowset looking like:

a b c
--------------
.
.
.

quote:

quote:

Just CROSS JOIN the lot of 'em.

setBasedIsTheTruepath
<O>

Okay, how do I do that?

Never stop learning!

setBasedIsTheTruepath
<O>

ewade
Starting Member

36 Posts

Posted - 2002-10-10 : 14:48:34

Wow, it's been a while since I was last here. Thanks again to everyone for the help. I was able to "cheat" and use Crystal Reports to generate the summary charts. Not as elegant as I would have liked, but I was running out of time.

BTW, I took a series of DBA courses over the summer and got up to speed, so hopefully my newer questions won't be as boneheaded.

Never stop learning!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources