Win a copy of Head First Android this week in the Android forum!
  • 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
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

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: 1093
19
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."
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic