Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Finding rows in a specific column of a given table

 
navi kumar
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a table named
RECORD with columns( id varchar2(8), name varchar2(45), number NUMBER, category varchar2(45), file_content BLOB default=EMPTY_BLOB)

Each value in FILE_CONTENT field corresponds to specific value in CATEGORY field. Say for ex: file1 belongs to 'marketing' category.

Now I want retrieve the count of rows in file_content field basing on its corresponding category field value. There are 6 values allowed in the CATEGORY field.

One sol'n i find is to count the similar values in the CATEGORY field i.e, how many times 'marketing' is present and give the count of it. I dont understand how to write the code for this. Help me here.

The problem with the above sol'n is ..if a new value is added to the CATEGORY field, the code should be added in the application too!!! Please lemme know, if there are any alternatives!

PS1: Earlier, we were not having the CATEGORY field and the count for the entire table ws given.
try {
// Select the number of rows in the table
Statement stmt = connection.createStatement();
ResultSet resultSet = stmt.executeQuery("SELECT COUNT(*) FROM RECORD");

// Get the number of rows from the result set
resultSet.next();
int rowcount = resultSet.getInt(1);
} catch (SQLException e) {
}


PS2: APART from giving the count, i need to give the user a list of files(present in the FILE_content field) for each category. I mean to say, I need to give the user 6 lists( one for each category) with the corresponding files(BLOB in the FILE_CONTENT).

Any help is appreciated.

regards
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Look into the GROUP BY clause. I'm rusty but bet you could just add GROUP BY CATEGORY to your query and get what you're lookin for.
 
navi kumar
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, We can upload the fields required using group by ...but how about giving the count of them??
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by navi kumar:
Yes, We can upload the fields required using group by ...but how about giving the count of them??


Count(*) works with a group by clause
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic