Creating a Total Count of a Column

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)

Creating a Total Count of a Column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-02 : 11:05:21

Kathy writes "I am trying to create a total in a table shown below.


State  Total
KS        2
NE        2
CO        5

Total     9

I have a procedure like this which returns the Total count of the states,but not the total on the bottom. I want to create the Total (9) at the bottom as shown with my query. This is a varchar I am using for state.

My query:

select state as [State], Count(states) as [Total]
from UserTable"

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 11:21:02

You want to use the compute clause....

select state as [State], Count(states) as [Total]
from UserTable
compute Count(states)

I think that should be right... look it up in Books online if you need more info

-----------------------
Take my advice, I dare ya

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 11:21:44

Mod, please edit formatting...

<O>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 11:24:17

quote:

Kathy writes "I am trying to create a total in a table shown below.

State Total
KS 2
NE 2
CO 5

Total 9

I have a procedure like this which returns the Total count of the states,but not the total on the bottom. I want to create the Total (9) at the bottom as shown with my query. This is a varchar I am using for state.

My query:

select state as [State], Count(states) as [Total]
from UserTable"

You may want something like


select state as [State], Count(states) as [Total] 
from UserTable
group by state
compute sum(count(states)) as Total

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 11:30:01

[edit] stupid double post.. just read below.. Hey, just upping my post count

[/edit]

Edited by - M.e. on 07/02/2002 11:31:03

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 11:30:10

quote:
Mod, please edit formatting...

No clue what that means :P

Ya I forgot the group by... thanks for pointing that out page

-----------------------
Take my advice, I dare ya

Kathy Schmid
Starting Member

3 Posts

Posted - 2002-07-02 : 12:23:35

Create Procedure sp_monthlyStudentReport

( @begindate datetime,
@enddate datetime)

as

select distinct status as Status,count(Status) as Total

from StudentRecords
where datecaseopened

between @begindate and @enddate

and fromstate ='KS'
and fromstate is not null

or status='New Student'
or status='Existing Student'

group by status
compute sum(count(status))

This is the procedure. It is working and returning the results and showing a sum of the Status. But it is not displaying on my ASP page as sum 25 or whatever the number result is.

Can I get the sum to show on my page in the table at the bottom.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 13:21:06

Hmm, I'm not sure if ASP can read the compute by bit.

2 ways around it...Would it be possible to just open a second record set?
select count(status) as total from from StudentRecords
where datecaseopened
between @begindate and @enddate
and fromstate ='KS'
and fromstate is not null
or status='New Student'
or status='Existing Student'

or the second option is to calculate the total in your asp page.
I'm guessing you have something along the lines of (if your not doing a loop like this... ignore it)

while not rs.EOF
----Misc coding to display info about each state
rs.next
wend

You can create a variable and increment it through the loop
while not rs.EOF
----Same misc coding
countvar = countvar + RS("Total")
rs.next
wend

Then at the end of your page countvar will have the total that you wanted.

-----------------------
Take my advice, I dare ya

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 13:32:25

quote:
Mod, please edit formatting...

Was a request to Merkin or Rob or Graz to edit the original post so that we can see the whole thing ...

quote:
But it is not displaying on my ASP page as sum 25 or whatever the number result is.

The compute will be in a second recordset, so you will net to cmd.nextrecordset or whatever (I ain't no stinkin programmer...) If you run the proc in query analyser, it'll be clear what's going on....

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-02 : 13:43:20

Hmm, might just be the last in the recordset.. That'd make sense

quote:
I ain't no stinkin programmer...

So that means you
a) Are a stinkin programmer
b) grade 6 english dropout :P
c) Both a and b

-----------------------
Take my advice, I dare ya

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-02 : 13:46:58

quote:

Hmm, might just be the last in the recordset..

Naw, that there'll be in a dif'rnt rs altogether, not the last row in the current rs.

And know, I can't speel for shyt.

<O>

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2002-07-02 : 15:19:05

This may work for you:

Create Procedure sp_monthlyStudentReport

(@begindate datetime,
@enddate datetime)

as

select 'SortOrder' = 1, status as Status, count(Status) as Total
from StudentRecords
where datecaseopened between @begindate and @enddate
and fromstate ='KS'
and fromstate is not null
or status='New Student'
or status='Existing Student'
group by status
union all
select 99999, 'Total', count(Status)
from StudentRecords
where datecaseopened between @begindate and @enddate
and fromstate ='KS'
and fromstate is not null
or status='New Student'
or status='Existing Student'
order by SortOrder, Status

Edited by - drymchaser on 07/02/2002 15:21:36

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-02 : 15:23:29

The ADO method call NextRecordset() will work just fine to return the results of the COMPUTE.

Jonathan Boott, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources