wood burning stoves*
The moose likes JDBC and the fly likes how to write this query... count, unique, ?? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to write this query... count, unique, ??" Watch "how to write this query... count, unique, ??" New topic
Author

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

Jessica Sant
Sheriff

Joined: Oct 17, 2001
Posts: 4313

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

Joined: Mar 26, 2002
Posts: 32
You could try

select code, count(code) from table
group by code


SCJP1.2<br />SCWCD1.4<br />SCBCD1.3
Peter Rooke
Ranch Hand

Joined: Oct 21, 2004
Posts: 800

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!


Regards Pete
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
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."
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to write this query... count, unique, ??
 
Similar Threads
SQL Select: GROUP BY Having Count
rs.next() always return true even no result is querried
how to get num of rows in ResultSet
Retrieving count of Y and count of N from a single column in a table
SQL Count