Finding rows in a specific column of a given table

[navi kumar]

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]

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]

Yes, We can upload the fields required using group by ...but how about giving the count of them??

[stu derby]

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