oracle sql left join and count, sum, group by

13,821

Solution 1

i ended up doing something ghastly like below by left joining two subqueries. if someone has a better way of doing this, please post.

select t1.sp_question_id, t2.projectCount, t2.amount, t2.percentTotal   
from (select 
        q.sp_question_id 
      from questions q 
      where q.fiscal_year = 2014) t1

left join

(select 
   q.sp_question_id,
   count(p.project_id) as projectCount,
   sum(p.funding) as amount,
   round(sum(p.funding)/sum(sum(p.funding)) over() *100) as percentTotal 
from questions q 
   left join projects p on p.fiscal_year = q.fiscal_year  
   join objectives o on o.sp_objective_id = p.sp_objective_id 
     and o.sp_question_id = q.sp_question_id 
     and o.fiscal_year = p.fiscal_year 
   join funders f on p.funder_id = f.funder_id 
where f.funder_short_name ='foo' 
   and q.fiscal_year = 2014  
group by q.sp_question_id 
order by q.sp_question_id) t2
on t1.sp_question_id = t2.sp_question_id 

Solution 2

The problem you're having relates to one of the most unknown problems of sql: the transitivity of relationships.

You're doing an optional (outer) join between projects and questions, but you're asking for a mandatory (inner join) relationship between projects and objectives (and funders):

The transitivity system which calculates the intermediary resultsets gives prevalence to the inner join, which as a result basically means the outer join in the middle is ignored. To be exact, it is not ignored, but it becomes an inner join instead.

What you get is the result of an inner join all the way down, while you're actually expecting the left join to behave as is and return null rows for the questions not related to any projects... But the engine does not work like this, as described above.

Out of my head, I just think you could use left joins all the way down. But then you probably would face more null rows than you expect. It really depends what you're doing with the data.

Also, it seems there's a broad join in your query that should be modified. Please try this:

select 
   q.sp_question_id,
   count(p.project_id) as projectCount,
   sum(p.funding) as amount,
   round(sum(p.funding)/sum(sum(p.funding)) over() *100) as percentTotal 
from questions q 
   left join objectives o on 
         o.sp_question_id = q.sp_question_id 
     and o.fiscal_year = 2014 
   left join projects p on o.fiscal_year = p.fiscal_year and o.sp_objective_id = p.sp_objective_id 
   left join funders f on p.funder_id = f.funder_id and f.funder_short_name ='foo' 
where q.fiscal_year = 2014 
group by q.sp_question_id 
order by q.sp_question_id;
Share:
13,821
milesmiles55
Author by

milesmiles55

Updated on June 04, 2022

Comments

  • milesmiles55
    milesmiles55 almost 2 years

    i want to calculate the project count, amount, and percent total for each question restricted to a given funder and fiscal year. for some reason when i run the query below, i'm not seeing the null values from the left join.

    the relationship between projects and questions is that a question can have many projects. the questions table is linked by the objectives table.

    UPDATED

    select 
       q.sp_question_id,
       count(p.project_id) as projectCount,
       sum(p.funding) as amount,
       round(sum(p.funding)/sum(sum(p.funding)) over() *100) as percentTotal 
    from questions q 
       left join projects p on p.fiscal_year = q.fiscal_year  
       join objectives o on o.sp_objective_id = p.sp_objective_id 
         and o.sp_question_id = q.sp_question_id 
         and o.fiscal_year = p.fiscal_year 
         and o.fiscal_year = 2014  
       join funders f on p.funder_id = f.funder_id 
    where f.funder_short_name ='foo' 
       and q.fiscal_year = 2014  
    group by q.sp_question_id 
    order by q.sp_question_id;
    

     questionId   projectCount  amount          percentTotal
     q1           14            54510           4
     q2           29            1083598.72      76
     q3           1             19900           1
     q4           5             145631          10
     q5           1             124999          9
     q6           1             0               0 
    

    expected result

     questionId   projectCount  amount          percentTotal
     q1           14            54510           4
     q2           29            1083598.72      76
     q3           1             19900           1
     q4           5             145631          10
     q5           1             124999          9
     q6           1             0               0
     q7       <null>            <null>          <null>
    

    query with left joins all the way down

    select 
           q.sp_question_id,
           count(p.project_id) as projectCount,
           sum(p.funding) as amount,
           round(sum(p.funding)/sum(sum(p.funding)) over() *100) as percentTotal 
        from questions q 
           left join projects p on p.fiscal_year = q.fiscal_year  
           left join objectives o on o.sp_objective_id = p.sp_objective_id 
             and o.sp_question_id = q.sp_question_id 
             and o.fiscal_year = p.fiscal_year 
             and o.fiscal_year = 2014  
           left join funders f on p.funder_id = f.funder_id 
        where f.funder_short_name ='foo' 
           and q.fiscal_year = 2014  
        group by q.sp_question_id 
        order by q.sp_question_id;
    

    result

     questionId   projectCount  amount          percentTotal
     na           51            1428638.72      11
     q1           51            1428638.72      11
     q2           51            1428638.72      11
     q3           51            1428638.72      11
     q4           51            1428638.72      11
     q5           51            1428638.72      11
     q6           51            1428638.72      11
     q7           51            1428638.72      11
     qother       51            1428638.72      11
    

    query with left joins all the way down and where clauses adjusted

    select 
       q.sp_question_id,
       count(p.project_id) as projectCount,
       sum(p.funding) as amount,
       round(sum(p.funding)/sum(sum(p.funding)) over() *100) as percentTotal 
    from questions q 
       left join projects p on p.fiscal_year = q.fiscal_year 
       left join objectives o on o.sp_objective_id = p.sp_objective_id 
         and o.sp_question_id = q.sp_question_id 
         and o.fiscal_year = p.fiscal_year and o.fiscal_year = 2014 
       left join funders f on p.funder_id = f.funder_id and f.funder_short_name ='foo' 
       where q.fiscal_year = 2014 
    group by q.sp_question_id 
    order by q.sp_question_id;
    

    result

     questionId   projectCount  amount          percentTotal
     na           1225          299628985.01    11
     q1           1225          299628985.01    11
     q2           1225          299628985.01    11
     q3           1225          299628985.01    11
     q4           1225          299628985.01    11
     q5           1225          299628985.01    11
     q6           1225          299628985.01    11
     q7           1225          299628985.01    11
     qother       1225          299628985.01    11
    
  • milesmiles55
    milesmiles55 over 9 years
    this makes good sense. i tried using left joins all the way down but, it's not working @Sebas
  • milesmiles55
    milesmiles55 over 9 years
    the post has been updated. all of the questionIds are coming back on the left side but, the aggregate functions for each row is off. the amounts given are the totals for the funder. @Sebas
  • Sebas
    Sebas over 9 years
    @milesmiles55 you need to add the conditions of the where clause to the left joins they belong. Otherwise the outer joins become inner join again, due to the fact that the where clause is considered mandatory.
  • milesmiles55
    milesmiles55 over 9 years
    updated @Sebas seems like where clauses are missing, can't put my finger on it
  • Sebas
    Sebas over 9 years
    @milesmiles55 Comment out the windowed function line (percentTotal ). It might be the reason why you get the rolled up results. After that if it's the problem we can figure another way to do it
  • milesmiles55
    milesmiles55 over 9 years