Simple Table Count - 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

Transact-SQL (2000)

Simple Table Count

Author

Topic

harrisw48
Starting Member

34 Posts

Posted - 2007-03-08 : 14:26:44

Im trying to create a query that creates a simple count with a total at the bottom

The field Im trying to count by does have NULLS in it.

When I run the code below the rollup total doesnt add up to the amount of records in the table

Whats wrong?

select [field name],
count([field name]) as [Count]
from table
group by [fielname] with rollup

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-08 : 14:44:30

As you said the field you're counting has nulls in it and nulls are excluded from aggregates like count. But you also said you're looking for the number of records in the table, not the count of the field, so use count(*) instead of count([field name]).

harrisw48
Starting Member

34 Posts

Posted - 2007-03-08 : 15:19:49

No Count * isnt what I want.
In access this is simple by having the field twice and grouping by one this will give you the count of the grouping of the field,

I want the query to be similar to this :

farming 6
carpentry 10
Total 16

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-08 : 16:05:28

Have you tried it, count(*) will give you exactly what you want

select [field name],
count(*) as [Count]
from table
group by [field name] with rollup

This would give you


farming    6
carpentry 10
NULL      16

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources