sql multiple count

Question

Hi, I have 3 tables, where the first one's primary key, is the foreign key in the other 2.

I want to extract one field from the first table, and then a count from the other 2, all joined using the pk and fk. This is what I have so far:

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) AS NUMBER_OF_GROUPS 
FROM MBDDX_TREATMENT_GROUP) 
ON MBDDX_TREATMENT_GROUP.STUDY_ID = MBDDX_STUDY.ID
group by MBDDX_STUDY.STUDY_NAME, MBDDX_TREATMENT_GROUP.STUDY_ID

But, i get an error saying that the MBDDX_TREATMENT_GROUP.STUDY_ID , in the penultimate line is an invalid indentifier. It is a correct table.

Any advise please.

Thanks.

Answer 1

You're getting the error because that column is not in your SELECT, so it can't GROUP BY a field it doesn't have.

Answer 2

You don't need to group by this field (MBDDX_TREATMENT_GROUP.STUDY_ID). It should be just group by MBDDX_STUDY.STUDY_NAME

Answer 3

If my understanding is correct,You need a record from first table and have the count of related records in the other two tables.Here is the answer

http://stackoverflow.com/questions/3835678/sqlgetting-count-from-many-tables-for-a-user-record-in-user-table-whats-the-best

Answer 4

It looks like you need to alias the second subquery and need to include something to join on.

It also looks like you aren't using the count you have in the subquery as well. Try this out:

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 MBDDX_TREATMENT_GROUP.STUDY_ID) xx 
ON xx.STUDY_ID = MBDDX_STUDY.ID 
GROUP BY MBDDX_STUDY.STUDY_NAME, xx.STUDY_ID

Answer 5

The subquery syntax doesn't seem to make any sense to me. You've made a query that counts all rows of MBDDX_TREATMENT_GROUP, independently of the STUDY_ID, and then tries to join it into the table with a join condition that doesn't refer to anything in the subquery's results (and can't, without an alias).

Why not use a simple join? Assuming MBDDX_EXPERIMENT also has a primary key ID, you can do it with a COUNT-DISTINCT:

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

(MBDDX_STUDY.STUDY_NAME technically shouldn't be necessary to include in the GROUP BY expression according to ANSI SQL as it has a functional dependency on STUDY_ID. However it is necessary on Oracle, which can't spot the dependency.)

Answer 6

For what you really want to do, you want OUTER JOINs.

WITH   number_of_experiments
AS     ( SELECT study_id
              , count ( * ) CNT
           FROM MBDDX_EXPERIMENT
          group by study_id )
     , number_of_groups
as     ( select study_id
              , count ( * ) CNT
           FROM mbddx_treatment_group
          group by study_id )
select study_name
     , coalesce(noex.cnt,0)
     , coalesce(notr.cnt,0)
  from mbddx_study
 outer join number_of_experiments
as     noex
 using ( study_id )
 outer join number_of_groups
as     nogr
 using ( study_id )