Home All Groups Group Topic Archive Search About

get counts for each unique value in a column

microsoft.public.sqlserver.programming
Author
13 Sep 2006 10:59 PM
mahalie
I'd like to get the count for each of 24 distinct values in a table
with 3,200 records in it without having to explicitly query for each of
the distinct values.  Is there some sort of "short cut" for this,
perhaps using group by?  Something like...SELECT
IssueType,SUM(count(IssueType)) FROM SolvedIssues GROUP BY IssueType??
Basically I'd like to know how many times each value in the column
IssuesType is repeated.

Here's the ddl and 20 rows of sample data:

CREATE DATABASE TestHelpApp
GO
USE TestHelpApp
GO

CREATE TABLE [SolvedIssues] (
    [IssueNumber] [int] NOT NULL ,
    [IssueType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
    [Priority] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DateSubmitted] [datetime] NULL ,
    [DateResolved] [datetime] NULL ,
    CONSTRAINT [PK_SolvedIssues] PRIMARY KEY  NONCLUSTERED
    (
        [IssueNumber]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(83,'CAD
Help','1 - High','Jan 29 2001  3:19:46:810PM','Jan 29 2001
3:44:39:793PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(56,'Hardware','2
- Medium','Jan 16 2001  3:23:30:653PM','Jan 16 2001  3:23:59:873PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(90,'Other','4
- Informational','Feb  1 2001  9:59:57:153AM','Feb  1 2001
10:39:10:700AM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(92,'CAD
Help','1 - High','Feb  1 2001 11:23:38:357AM','Feb  2 2001
3:33:27:187PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(111,'Printers','2
- Medium','Feb  6 2001 12:48:09:840PM','Feb  6 2001 12:48:56:123PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(121,'Software','2
- Medium','Feb  6 2001  5:50:21:247PM','Feb  6 2001  5:54:58:293PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(119,'Software','2
- Medium','Feb  6 2001  4:18:18:293PM','Feb  6 2001  4:19:35:797PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(144,'Software','3
- Low','Feb  8 2001  9:01:46:920AM','Feb  9 2001  3:55:20:810PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(67,'Software','2
- Medium','Jan 18 2001 11:19:04:547AM','Jan 18 2001 11:19:21:623AM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(115,'Printers','2
- Medium','Feb  6 2001  3:46:05:513PM','Feb  6 2001  3:50:56:077PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(86,'Software','2
- Medium','Jan 30 2001 10:43:58:500AM','Feb  1 2001 10:53:05:920AM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(120,'Software','2
- Medium','Feb  6 2001  5:49:39:513PM','Feb  6 2001  5:50:59:983PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(154,'Software','2
- Medium','Feb 14 2001  3:51:52:000PM','Feb 14 2001  5:16:31:873PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(141,'Hardware','2
- Medium','Feb  7 2001 11:55:54:187AM','Feb 14 2001  5:18:27:280PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(142,'Software','2
- Medium','Feb  7 2001  4:11:37:343PM','Feb 14 2001  5:22:05:343PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(109,'Software','1
- High','Feb  5 2001  3:18:05:200PM','Feb 14 2001  5:25:48:653PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(160,'Hardware','2
- Medium','Feb 15 2001 11:02:48:000AM','Feb 15 2001 11:09:27:780AM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(163,'Software','2
- Medium','Feb 15 2001  3:56:37:000PM','Feb 15 2001  4:12:33:373PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(164,'Software','1
- High','Feb 16 2001  2:25:28:000PM','Feb 16 2001  2:57:12:467PM')
INSERT INTO [solvedissues]
([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(168,'Other','2
- Medium','Feb 20 2001  5:47:39:000PM','Feb 21 2001  8:16:13:123AM')

Author
13 Sep 2006 11:08 PM
Ryan
Have you tried...
Select  count(distinct SolvedIssues), count(distinct IssueNumber),
count(distinct Priority), count(*)
From ....

Ryan

Show quoteHide quote
"mahalie" wrote:

> I'd like to get the count for each of 24 distinct values in a table
> with 3,200 records in it without having to explicitly query for each of
> the distinct values.  Is there some sort of "short cut" for this,
> perhaps using group by?  Something like...SELECT
> IssueType,SUM(count(IssueType)) FROM SolvedIssues GROUP BY IssueType??
> Basically I'd like to know how many times each value in the column
> IssuesType is repeated.
>
> Here's the ddl and 20 rows of sample data:
>
> CREATE DATABASE TestHelpApp
> GO
> USE TestHelpApp
> GO
>
> CREATE TABLE [SolvedIssues] (
>     [IssueNumber] [int] NOT NULL ,
>     [IssueType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
>     [Priority] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>     [DateSubmitted] [datetime] NULL ,
>     [DateResolved] [datetime] NULL ,
>     CONSTRAINT [PK_SolvedIssues] PRIMARY KEY  NONCLUSTERED
>     (
>         [IssueNumber]
>     ) WITH  FILLFACTOR = 90  ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(83,'CAD
> Help','1 - High','Jan 29 2001  3:19:46:810PM','Jan 29 2001
> 3:44:39:793PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(56,'Hardware','2
> - Medium','Jan 16 2001  3:23:30:653PM','Jan 16 2001  3:23:59:873PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(90,'Other','4
> - Informational','Feb  1 2001  9:59:57:153AM','Feb  1 2001
> 10:39:10:700AM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(92,'CAD
> Help','1 - High','Feb  1 2001 11:23:38:357AM','Feb  2 2001
> 3:33:27:187PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(111,'Printers','2
> - Medium','Feb  6 2001 12:48:09:840PM','Feb  6 2001 12:48:56:123PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(121,'Software','2
> - Medium','Feb  6 2001  5:50:21:247PM','Feb  6 2001  5:54:58:293PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(119,'Software','2
> - Medium','Feb  6 2001  4:18:18:293PM','Feb  6 2001  4:19:35:797PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(144,'Software','3
> - Low','Feb  8 2001  9:01:46:920AM','Feb  9 2001  3:55:20:810PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(67,'Software','2
> - Medium','Jan 18 2001 11:19:04:547AM','Jan 18 2001 11:19:21:623AM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(115,'Printers','2
> - Medium','Feb  6 2001  3:46:05:513PM','Feb  6 2001  3:50:56:077PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(86,'Software','2
> - Medium','Jan 30 2001 10:43:58:500AM','Feb  1 2001 10:53:05:920AM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(120,'Software','2
> - Medium','Feb  6 2001  5:49:39:513PM','Feb  6 2001  5:50:59:983PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(154,'Software','2
> - Medium','Feb 14 2001  3:51:52:000PM','Feb 14 2001  5:16:31:873PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(141,'Hardware','2
> - Medium','Feb  7 2001 11:55:54:187AM','Feb 14 2001  5:18:27:280PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(142,'Software','2
> - Medium','Feb  7 2001  4:11:37:343PM','Feb 14 2001  5:22:05:343PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(109,'Software','1
> - High','Feb  5 2001  3:18:05:200PM','Feb 14 2001  5:25:48:653PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(160,'Hardware','2
> - Medium','Feb 15 2001 11:02:48:000AM','Feb 15 2001 11:09:27:780AM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(163,'Software','2
> - Medium','Feb 15 2001  3:56:37:000PM','Feb 15 2001  4:12:33:373PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(164,'Software','1
> - High','Feb 16 2001  2:25:28:000PM','Feb 16 2001  2:57:12:467PM')
> INSERT INTO [solvedissues]
> ([IssueNumber],[IssueType],[Priority],[DateSubmitted],[DateResolved])VALUES(168,'Other','2
> - Medium','Feb 20 2001  5:47:39:000PM','Feb 21 2001  8:16:13:123AM')
>
>
Are all your drivers up to date? click for free checkup

Author
13 Sep 2006 11:11 PM
David Portas
mahalie wrote:
> I'd like to get the count for each of 24 distinct values in a table
> with 3,200 records in it without having to explicitly query for each of
> the distinct values.  Is there some sort of "short cut" for this,
> perhaps using group by?  Something like...SELECT
> IssueType,SUM(count(IssueType)) FROM SolvedIssues GROUP BY IssueType??
> Basically I'd like to know how many times each value in the column
> IssuesType is repeated.
>

SELECT IssueType, COUNT(*) AS cnt
FROM SolvedIssues
GROUP BY IssueType ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Author
13 Sep 2006 11:19 PM
mahalie
> > Something like...SELECT
> > IssueType,SUM(count(IssueType)) FROM SolvedIssues GROUP BY IssueType??
> > Basically I'd like to know how many times each value in the column
> > IssuesType is repeated.
> >
>
> SELECT IssueType, COUNT(*) AS cnt
>  FROM SolvedIssues
>  GROUP BY IssueType ;

Doh! I was so close.  Thanks a lot!!
Author
14 Sep 2006 12:10 AM
Arnie Rowland
You were VERY close.

And a special thanks for making the effort to include the table DDL and the
sample data. If everyone followed your example, we would be able to offer
help more often and quicker.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


Show quoteHide quote
"mahalie" <maha***@gmail.com> wrote in message
news:1158189562.709889.153360@e3g2000cwe.googlegroups.com...
>> > Something like...SELECT
>> > IssueType,SUM(count(IssueType)) FROM SolvedIssues GROUP BY IssueType??
>> > Basically I'd like to know how many times each value in the column
>> > IssuesType is repeated.
>> >
>>
>> SELECT IssueType, COUNT(*) AS cnt
>>  FROM SolvedIssues
>>  GROUP BY IssueType ;
>
> Doh! I was so close.  Thanks a lot!!
>

Bookmark and Share

Post Other interesting topics
Bulk Insert To Temp Table - Security Issue
Deleting a record dynamically
What am I missing with this simple query?
Passing username to sql server from an app
UPDATE TOP 1
For insert trigger problem
Is it possible to trace all statements to a specific table?...
Any need for TEXT/NTEXT column types in SQL2005
what is the code to see the all the indexes
Joined Query With Optional Data