File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes SQL Select: GROUP BY Having Count Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Select: GROUP BY Having Count" Watch "SQL Select: GROUP BY Having Count" New topic

SQL Select: GROUP BY Having Count

Brian R. Wainwright
Ranch Hand

Joined: Aug 12, 2003
Posts: 92
Hey everyone,

I'm having difficulty resolving a query in Oracle 8i...

I have the following table:

The primary key is a combination of all 3 columns.
The module_type column can be one of four values: 4, 5, 6 or 7.
I'm attempting to select those employee_ids that exist in ALL 4 module_types for a given number of company_id's. For example, given the following data:

The following query returns employee_id 1

This query works great. I need to further filter the result set however, by module_type so that if no record exists for a given module type, no results are returned. In other words, in order to obtain a result set there must be at least 1 row for each module_type.

Does anyone have any suggestions for further filtering this result set? Thanks!
[ June 01, 2006: Message edited by: Brian R. Wainwright ]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
For your further restricted query, try this:

4 modules means 4 rows for each employee of that company.
Brian R. Wainwright
Ranch Hand

Joined: Aug 12, 2003
Posts: 92
Thanks stu...

I've actually refined the query a little further...

So this is doing something similar, but checking another table (ATTRIBUTE_VALUES) to get a count of the number of rows with a value of 'TRUE'

This number is used to make certain that the number of the number I get in the second sub-select is valid. The last piece here is to pass that value into the group by clause like this:

The problem with this last statement is that I get a "NOT A GROUP BY Expression" when adding the last select. Oracle doesn't like the " = ugd.company_id". If I replace the = ugd.company_id with a number (51) it works. This number however needs to be whatever the current ugd.company_id is. If I got this to work, I guess I also wouldn't like that it does the same query twice. But I could live with that. I know I should be able to have a dynamic value as part of my HAVING COUNT(employee_id) = clause, but I'm not sure if it is possibel in this context. Any more ideas would be great! Thanks again!
[ June 02, 2006: Message edited by: Brian R. Wainwright ]
Brian R. Wainwright
Ranch Hand

Joined: Aug 12, 2003
Posts: 92
Ok... well I think I figured out the problem... pretty simple actually. I just need to add ugd.company_id to my GROUP BY clause... I guess because it is being included as part of the SELECT somehow? I not really certain why. Does anyone have an explanation?
I agree. Here's the link:
subject: SQL Select: GROUP BY Having Count
It's not a secret anymore!