Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to write this query... count, unique, ??

 
Jessica Sant
Sheriff
Posts: 4313
Android IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So I've got a DB that's got all these codes in it.

I can write
--- select unique CODE from TABLE;
and I get a list of all the different kinds of codes in the table

I can write
--- select count( CODE ) from TABLE;
and I get the number of codes in the whole table.

I can write
--- select count (unique CODE) from TABLE;
and I get the number of differnt codes in the table.

BUT -- how do I write a query that will tell me all the unique codes and how many instances there is of that code in the DB. So I want my result to look like this:


I'm having quite the brain fart on this one.. I can't remember my basic SQL... Thanks!!
 
Sreenath Madasu
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could try

select code, count(code) from table
group by code
 
Peter Rooke
Ranch Hand
Posts: 847
1
Java Linux Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
GROUP BY, so:-



Note - I've called the count(*) column CODE_COUNT as 'count' is a SQL term. It may work with 'count'.
-----
Of complication, despond, and general distress. Are two nulls equal?. I fear both no and yes!
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1817
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Note that count(*) and count(code) will differ in how they treat nulls. You may or may not care.

--- select count( CODE ) from TABLE;
and I get the number of codes in the whole table.


Another way of thinking of that is "and I get the number of rows on the table where code is not null."
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic