• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

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

 
Sheriff
Posts: 4313
Android IntelliJ IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!!
 
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You could try

select code, count(code) from table
group by code
 
Bartender
Posts: 1161
20
Mac OS X IntelliJ IDE Oracle Spring VI Editor Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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."
 
reply
    Bookmark Topic Watch Topic
  • New Topic