SQL, Inner join issue in oracle

Question

^ HOSPITAL_SPECIALISE_TEST ^

^ HOSPITAL ^

Here are my 2 tables. I'm trying to use inner join to generate a result and this is the query I currently use :

SELECT HOSPITAL_ID, COUNT(DISTINCT SPECIALISE) AS SPECIALISE FROM HOSPITAL_SPECIALISE_TEST GROUP BY HOSPITAL_ID;

And I get the result below...

Now here is the question, I want to join both tables and display hospital name, but it fails to generate and I don't know why... Here is my query:

SELECT HS.HOSPITAL_ID, HP.NAME, COUNT(DISTINCT HS.SPECIALISE) AS SPECIALISE 
FROM HOSPITAL_SPECIALISE_TEST HS
INNER JOIN HOSPITAL HP 
on HS.HOSPITAL_ID = HP.HOSPITAL_ID 
GROUP BY HOSPITAL_ID;

"but it fails to generate" is a weak description of what is happening. You should tell us the Oracle error instead ("ORA-00979 not a GROUP BY expression"). — Thorsten Kettner
– Thorsten Kettner, Commented Apr 7, 2016 at 20:39

cableload · Accepted Answer · 2016-04-07 20:12:24Z

1

Do this instead

SELECT HP.NAME, spec.hospital_id, spec.specialise
FROM (  SELECT HOSPITAL_ID, COUNT (DISTINCT SPECIALISE) AS SPECIALISE
        FROM HOSPITAL_SPECIALISE_TEST
    GROUP BY HOSPITAL_ID) spec
   INNER JOIN HOSPITAL HP ON spec.HOSPITAL_ID = HP.HOSPITAL_ID

answered Apr 7, 2016 at 20:12

cableload

4,4156 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thorsten Kettner · Accepted Answer · 2016-04-07 20:34:31Z

Although the hospital name can be unambiguously concluded from the hospital ID you are grouping by, Oracle demands it to be either also included in the GROUP by clause or be aggregated. This leads to one of these rather clumsy queries:

select hs.hospital_id, hp.name, count(distinct hs.specialise) as specialise 
from hospital_specialise_test hs
inner join hospital hp on hs.hospital_id = hp.hospital_id 
group by hs.hospital_id, hp.name;

or

select hs.hospital_id, max(hp.name) as name, count(distinct hs.specialise) as specialise 
from hospital_specialise_test hs
inner join hospital hp on hs.hospital_id = hp.hospital_id 
group by hs.hospital_id;

This is compliant with the old SQL 92 standard, but not with newer versions of the SQL standard which would happily accept your query :-)

You can circumvent the issue by not joining the tables:

select 
  hs.hospital_id, 
  (select name from hospital hp where hs.hospital_id = hp.hospital_id) as name,
  count(distinct hs.specialise) as specialise 
from hospital_specialise_test hs
group by hospital_id, hp.name;

or by joining pre-aggregated records, as suggested by cableload.

T Gray · Accepted Answer · 2016-04-07 20:12:40Z

0

The group by must contain all select columns (other than the aggregate):

GROUP BY HS.HOSPITAL_ID, HP.NAME.

answered Apr 7, 2016 at 20:12

T Gray

7144 silver badges10 bronze badges

Collectives™ on Stack Overflow

SQL, Inner join issue in oracle

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related