Count of Unique or Distinct Types

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

General SQL Server Forums

New to SQL Server Programming

Count of Unique or Distinct Types

Author

Topic

jcervantes
Starting Member

14 Posts

Posted - 2011-07-27 : 10:12:43

Hello all, this is my first post and I am not sure I am even asking this correctly.

What I am trying to accomplish is to get a count of each distinct item in a field.

Example

I've got a field called "Codes" see below.

Codes
10 Emergency
2 Test
10 Emergency
2 Test
2 Test
1 Help

Id like to get
10 Emergency = 2
2 Test = 3
1 Help = 1

Any help would be appreciated!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-27 : 10:19:21

SELECT Codes, COUNT(*) FROM myTable GROUP BY Codes

jcervantes
Starting Member

14 Posts

Posted - 2011-07-27 : 10:27:39

quote:
Originally posted by robvolk

SELECT Codes, COUNT(*) FROM myTable GROUP BY Codes

That did it! Thanks for the quick help!!!

jcervantes
Starting Member

14 Posts

Posted - 2011-07-27 : 16:42:28

Ok, so I have another question regarding this.

That statement works perfect by itself, but when I add it to my whole query it gets messed up and doesn't show all the data i want.

SELECT
Job,
GenDept,
CC,
Cost,
Code,
COUNT(Code),
Description,
Date
FROM
PONCDB.Form
GROUP BY
Code

That query shows all the data in my table with distinct codes and the count. Is there a way to get this info in one query? or will I have to divide things up?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-27 : 16:48:29

SELECT Job,GenDept,CC,Cost,Code, COUNT(Code) OVER (PARTITION BY Code), Description,Date
FROM PONCDB.Form

jcervantes
Starting Member

14 Posts

Posted - 2011-07-28 : 08:34:35

quote:
Originally posted by robvolk

SELECT Job,GenDept,CC,Cost,Code, COUNT(Code) OVER (PARTITION BY Code), Description,Date
FROM PONCDB.Form

Not sure what im doing wrong, I get the following error.

ERROR: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right
syntax to use near '(PARTITION BY Code),
Description,
Date
FROM
PONCDB.Form'
at line 7
Error Code: 1064

Query = SELECT
Job,
GenDept,
CC,
Cost,
Code,
COUNT(Code)
OVER (PARTITION
BY Code),
Description,
Date
FROM
PONCDB.Form

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 09:25:20

Which version of SQL Server are you using?

jcervantes
Starting Member

14 Posts

Posted - 2011-07-28 : 09:46:34

quote:
Originally posted by robvolk

Which version of SQL Server are you using?

MYSQL 5.5

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 09:50:50

Um, well, SQLTeam is a Microsoft SQL Server website, we don't answer MySQL questions. You can visit the MySQL forum at http://dbforums.com/ for more help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources