Cube? Return counts of values for each distinct column value

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

Analysis Services (2000)

Cube? Return counts of values for each distinct column value

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-02 : 09:12:33

Norman writes "I know that this can be accomplished with an sql statement, can you help me?
An example sql statement would really help. Here is my table (tblArrests):
--------------------------------------------------------

Officer                                                   Law
--------                                               -----------
O'BRADY                                           LOCAL LAW
O'BRADY                                           LOCAL LAW
O'BRADY                                           Mental Health 
O'BRADY                                           Other Law
THOMPSON                                          V&T Law

---------------------------------------------------------
The end result should be the officers name followed by a count of how many
laws correspond to each officer, so the above table result should be:
O'BRADY
Local = 2
Mental Health = 1
Other = 1
THOMPSON
V&T Law = 1"

MikeArnold
Starting Member

11 Posts

Posted - 2002-04-08 : 02:28:15

try:
SELECT Officer, Law, COUNT(*) FROM tblArrests GROUP BY Officer, Law

sandesh_moghe
Constraint Violating Yak Guru

310 Posts

Posted - 2002-04-15 : 04:11:41

If you really want the result as shown in your query, i.e. Officer name should not be repeate, then cursor is the best option.
Or if you are using SQL Server 2000, then you can user XML with XSL.

---------------------------
Sandesh - The Messanger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources