get counts for each unique value in a column

microsoft.public.sqlserver.programming

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')

13 Sep 2006 11:08 PM

Ryan

Have you tried...
Select count(distinct SolvedIssues), count(distinct IssueNumber),
count(distinct Priority), count(*)
From ....

Ryan

Show 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')
>
>

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
--

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!!

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 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!!
>