Storing count in table - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Storing count in table

Author

Topic

mkh
Starting Member

18 Posts

Posted - 2004-07-06 : 05:51:28

HI ALL,

This seems simple
I want to store a count value directly in table.

SELECT COUNT(*) AS total FROM accounts
WHERE number > 500;

UPDATE sumofaccounts set totalnos = totalnos+total where totalnos>='1';

assuming where is true always.

but i get data mismatch error where total is of datatype varchar
can anyone give me a solution .

regards
MKH.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-06 : 05:54:57

This is assuming totalnos is an int datatype...


declare @total int

SELECT @total = COUNT(*) FROM accounts
WHERE number > 500 

UPDATE sumofaccounts set totalnos = totalnos+@total where totalnos>=1

mkh
Starting Member

18 Posts

Posted - 2004-07-06 : 06:44:23

Hi

Thanks Rick for your answer

But what is the default value of count(*) as total as i cant define any variable where i need to write this code.
IS it of type varchar or int
so how can i convert the default bvalue & put in update statement
without any variable definition

regards
mkh

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-06 : 06:54:20

use cast:

select cast(count(*) as char(6)) from mytable

Duane.

mkh
Starting Member

18 Posts

Posted - 2004-07-06 : 07:51:55

Hi

Yes It will solve the problem of not defining a var
but how will i pass it on to update

regards
mkh

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-06 : 07:58:06

Using a slight modification of Ricks code might do it:

declare @total int
SELECT @total = COUNT(*) FROM accounts
WHERE number > 500

UPDATE sumofaccounts set totalnos = CAST(CAST(totalnos as INT) + @total as CHAR(6)) where totalnos>='1'

Duane.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 08:07:49

Sounds like a homework question to me. Why else could you not use a variable???



UPDATE soa
SET soa.totalnos = CAST(soa.totalnos AS INT) + a.total
FROM
	sumofaccounts soa
	CROSS JOIN (
		SELECT COUNT(*) AS total
		FROM accounts
		WHERE number > 500) a  
WHERE 
	soa.totalnos>='1'
	AND ISNUMERIC(soa.totalnos) = 1

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mkh
Starting Member

18 Posts

Posted - 2004-07-06 : 08:24:56

Hi,

That does not solve my problem.

I just need a select & update without a declare .

Select count(*)as abc ......
Update abc into tablename .

IS it possible?

regards
mkh.

mkh
Starting Member

18 Posts

Posted - 2004-07-06 : 08:27:41

HI Derrick,

Homework question??

because my tool does not support any declaration of variables
I have to write only Sql statement.

regards
mkh.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 08:32:54

??? That is a select and update without a declare. Do you need the exact statements given to you?



SELECT COUNT(*) AS total
FROM accounts
WHERE number > 500

UPDATE soa
SET soa.totalnos = CAST(soa.totalnos AS INT) + a.total
FROM
	sumofaccounts soa
	CROSS JOIN (
		SELECT COUNT(*) AS total
		FROM accounts
		WHERE number > 500) a  
WHERE 
	soa.totalnos>='1'
	AND ISNUMERIC(soa.totalnos) = 1

What tool are you using? You should be writing stored procedures for all your code. You can then just call the stored procedures from whatever tool you are using. I've never heard of a tool that doesn't support declaration of variables though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources