Oracle SQL help

Question

I posted on Friday (sql multiple count) and had a few responses.

Having tried to implement them today, I keep getting the same error.

My SQL code now is:

SELECT MBDDX_STUDY.STUDY_NAME,
       COUNT(MBDDX_EXPERIMENT.STUDY_ID)
AS     NUMBER_OF_EXPERIMENTS
FROM MBDDX_STUDY
INNER JOIN MBDDX_EXPERIMENT
       ON MBDDX_STUDY.ID = MBDDX_EXPERIMENT.STUDY_ID
INNER JOIN (SELECT COUNT(MBDDX_TREATMENT_GROUP.GROUP_NO)
            FROM MBDDX_TREATMENT_GROUP)
       ON MBDDX_TREATMENT_GROUP.STUDY_ID = MBDDX_STUDY.ID
GROUP BY MBDDX_STUDY.STUDY_NAME

I keep getting the error:

ORA-00904: "MBDDX_TREATMENT_GROUP"."STUDY_ID": invalid identifier

Is it because it is outside of the inner join bracket, i.e. out of scope? I am very new to SQL and cannot understand why it wont work. I can get it working using select subqueries (without joins) but I want to also be able to work with joins.

If it matters any I am using Toad for Oracle.

Thanks.

There doesn't seem to be anything new in this compared to your last question. Please edit/comment on your previous question rather than posting anew if it is essentially the same question. See my answer there for an approach that does not require subqueries at all. — bobince, Oct 25 '10 at 11:21
Thanks Bob. I have used your solution in the other thread and it works perfectly and is a lot cleaner than what I was trying. — Darren Young, Oct 25 '10 at 12:02
possible duplicate of [sql multiple count](http://stackoverflow.com/questions/3997919/sql-multiple-count) — APC, Oct 25 '10 at 16:00

Benoit · Accepted Answer · 2010-10-25T11:06:43.867

4

Because you join with a query. Give a name to that query, and refer to it that way:

SELECT MBDDX_STUDY.STUDY_NAME
     , COUNT ( MBDDX_EXPERIMENT.STUDY_ID )
AS     NUMBER_OF_EXPERIMENTS
  FROM MBDDX_STUDY
 INNER JOIN MBDDX_EXPERIMENT
    ON MBDDX_STUDY.ID = MBDDX_EXPERIMENT.STUDY_ID
 inner JOIN ( SELECT study_id, COUNT ( MBDDX_TREATMENT_GROUP.GROUP_NO )
            FROM MBDDX_TREATMENT_GROUP  group by study_id ) AS my_query
    ON my_query.STUDY_ID = MBDDX_STUDY.ID
 GROUP BY MBDDX_STUDY.STUDY_NAME

edited Oct 25 '10 at 11:06

answered Oct 25 '10 at 11:00

Benoit

76,634
23
210
236

Thanks for the answer, I have just tried your suggestion and it now says: ORA-00905: missing keyword, This is caused by the AS my_query? Thanks – Darren Young Oct 25 '10 at 11:03
Sorry, there was a typo in my answer. You can check the edition log or paste it again. – Benoit Oct 25 '10 at 11:07
It works if I drop the AS keyword. Well, I say it works. It doesn't show me the new column. I guess that is a problem with my logic? – Darren Young Oct 25 '10 at 11:11
1

If you want something to be reported, you must state it in the most outer SELECT statement. Inner SELECTs create temporary views that are used for joining. – Benoit Oct 25 '10 at 11:14
Okay. Thanks for that. Within the outer select, I have tried writing my_query.group_no my_query.study_id , etc and nothing seems to work? Apologies for the many questions and thanks for taking the time to help. – Darren Young Oct 25 '10 at 11:29

score 2 · Answer 2 · answered Oct 25 '10 at 11:02

2

For one thing, a subquery must have an alias. Change:

inner JOIN ( SELECT COUNT ( MBDDX_TREATMENT_GROUP.GROUP_NO )
    FROM MBDDX_TREATMENT_GROUP )
ON MBDDX_TREATMENT_GROUP.STUDY_ID = MBDDX_STUDY.ID

to

inner JOIN ( SELECT COUNT ( MBDDX_TREATMENT_GROUP.GROUP_NO )
    FROM MBDDX_TREATMENT_GROUP ) as CountAlias
ON MBDDX_TREATMENT_GROUP.STUDY_ID = MBDDX_STUDY.ID

The second thing is that you have to include all columns you plan to use. Right now, the subquery just selects a count, but the ON clause references STUDY_ID. You can fix that by including STUDY_ID in the subquery select list, like:

inner JOIN ( 
    SELECT  STUDY_ID
    ,       COUNT(MBDDX_TREATMENT_GROUP.GROUP_NO) as GroupCount
    FROM MBDDX_TREATMENT_GROUP) as CountAlias
ON MBDDX_TREATMENT_GROUP.STUDY_ID = MBDDX_STUDY.ID

Now after that, you might hit other issues, but I'm hoping this will get you started.

answered Oct 25 '10 at 11:02

Andomar

232,371
49
380
404

I like that structured way of forming your answer. +1 for that. – Benoit Oct 25 '10 at 11:07
SELECT MBDDX_STUDY.STUDY_NAME , COUNT ( MBDDX_EXPERIMENT.STUDY_ID ) AS NUMBER_OF_EXPERIMENTS FROM MBDDX_STUDY INNER JOIN MBDDX_EXPERIMENT ON MBDDX_STUDY.ID = MBDDX_EXPERIMENT.STUDY_ID INNER JOIN ( SELECT study_id, COUNT ( MBDDX_TREATMENT_GROUP.GROUP_NO ) AS Number_of_groups FROM MBDDX_TREATMENT_GROUP group by study_id ) my_query ON my_query.STUDY_ID = MBDDX_STUDY.ID GROUP BY MBDDX_STUDY.STUDY_NAME But, the new column does not show in the table? Is that a problem with the join? Thanks – Darren Young Oct 25 '10 at 11:09
2

@Benoit: Thanks! @DarrenYoung: Try to add `myquery.Number_of_groups` to the select list, before the first `from`? – Andomar Oct 25 '10 at 11:11
No, that's not doing anything? Is my logic okay? – Darren Young Oct 25 '10 at 11:16

Oracle SQL help

2 Answers2