• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Select: GROUP BY Having Count

 
Brian R. Wainwright
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey everyone,

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

I have the following table:

CREATE TABLE COMPANY_EMPLOYEES
(
COMPANY_ID INTEGER DEFAULT NULL,
EMPLOYEE_ID INTEGER DEFAULT NULL,
MODULE_TYPE INTEGER
)
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
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For your further restricted query, try this:

4 modules means 4 rows for each employee of that company.
 
Brian R. Wainwright
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic