Stored procedure help needed.

Question

Post reply

Stored procedure help needed.

citrustrip

SSC Rookie

Points: 43
More actions
December 8, 2003 at 5:38 am

#160239

Hi,
I administer an SQL server database with an Access front end. The main table has 6000 directory entries, each entry has a category ID between 1 and 30. I'm trying to make a T-SQL stored procedure that returns a grid with the number entries in each category. I can use the COUNT function to calculate the number in any particular category, but not a breakdown of each category. Can someone please advise me how to do this? Do I need to use a loop and store the values in variables? If so how?
Thanks for your time,
Ben

Viewing 10 posts - 1 through 9 (of 9 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Not sure if I understand this, but would a look in BOL for 'ROLLUP' help.

Anyway, if you use Access as front end, why not take advantage of the grouping functionality in a report?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

DavidT SSCommitted Points: 1727 More actions · Answer 2

Unless I misunderstand, wouldn't something like this be what your after:

 

select 

    CategoryID, 

    Count(*) as [Count] 

from TableName

group by 

    CategoryID

Edited by - DavidT on 12/08/2003 06:19:13 AM

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

That's what I first thought, too and that's where my lack of english leaves me insecure.

Let's wait and see.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

citrustrip SSC Rookie Points: 43 More actions · Answer 4

Thanks guys,

As usual it was a lot more simple than I thought.

The following statement achieves exactly what I was after.

SELECT categoryID, COUNT(CategoryID) AS total

FROM entry

GROUP BY categoryID

ORDER BY categoryID ASC

I now need this data logging every day to a new table, whats the best way of doing this?

Thanks again,

Ben

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Why does it need to be a new table?

Can't you just use an additional datetime column to store the information and query this.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

Edited by - Frank kalis on 12/08/2003 08:00:11 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David Burrows SSC Guru Points: 65142 More actions · Answer 6

Agree with Frank.

(Wow did I really say that )

But if you do want to put the results in another table then

INSERT INTO LogTable 

(LogDate,CategoryID,total) 

SELECT GETDATE(), categoryID, COUNT(CategoryID) AS total 

...

Far away is close at hand in the images of elsewhere.
Anon.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Bah, to speak with my son:

quote:
You're not my friend anymore!

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David.Poole SSC Guru Points: 76000 More actions · Answer 8

When I was doing Access/SQL work I set up a passthrough query to SQL that ran my record aggregate stored procedure.

I then set up a cross-tab query that used the passthrough query as its source. This gave me the advantage of the speed of SQL together with the flexibility of the Access crosstab query.

I tended to let Access do the sorting as well.

Where you can guarantee that your aggregating data doesn't contain NULLs you can use WITH CUBE or WITH ROLLUP and do a select for particular NULL columns to bring back your results.

He was not wholly unware of the potential lack of insignificance.

LinkedIn Profile

DanaH1976 Ten Centuries Points: 1046 More actions · Answer 9

Access has a Crosstab query feature that should provide you with the exact report that you want. Go to the Queries tab, click New, and choose Crosstab from the query options.

Dana
Connecticut, USA
Dana