CODEWITHSUNDEEP
sqldistinctcaseinner-join
ja jaaaa
ja jaaaa

Reputation: 115

SQL - combining DISTINCT and INNER JOIN and COUNT(CASE WHEN)

I have SQL which return me correct result.

select accounts.name, count( case when ((( estimated_start_date<='2013-01-01' 
and project.status='closed')

or ( estimated_start_date<='2013-01-01' and project.status='open' 
and estimated_end_date>='2013-04-01')))
then 1 else null end) as Number

from project

inner join project_cstm on project.id=project_cstm.id_c
inner join accounts on project_cstm.account_id_c=accounts.id

group by accounts.name

I returns me good result:

On example:

Telenor 5
Telecom 3

Now I want to add new field BOOKED_DATE in WHERE clause to restrict number of projects only with some projects tasks created in in that interval. So I do INNER JOIN with table inner join project_task and results should be smaller but instead I am getting as result these huge numbers.Don not know how to combine it.

Thanks for help

Telenor 150
Telecom 980

select accounts.name, count( case when ((( estimated_start_date<='2013-01-01' 
and project.status='closed')

or ( estimated_start_date<='2013-01-01' and project.status='open' 
and estimated_end_date>='2013-04-01')))
then 1 else null end) as Number

from project

inner join project_cstm on project.id=project_cstm.id_c
inner join accounts on project_cstm.account_id_c=accounts.id
inner join project_task on project_task.project_id=project.id
where booked_date>'2013-01-01' and booked_date<'2015-01-01'
group by accounts.name

Because of rows in table PROJECT_TASK I am getting HUGE results which are far from correct. How to restrict that? I tried putting ... count(distinct case when ... but than result is always 1.

So I must somehow restrict this values but instead of that I am

Upvotes: 2

Views: 3651

Answers (3)

MikA
MikA

Reputation: 5552

Your joining table project_task might have duplicates in column 'project_id'

Upvotes: 0

Cyril Gandon
Cyril Gandon

Reputation: 17058

You are counting 1 for each task of your project, that's why you have this much.
For each project, you need to increment your count only 1 time.

You want to count the distinct projet, so instead of counting only 1's, count distinct project id's:

select accounts.name
     , count( distinct  -- here distinct
        case when ((( estimated_start_date<='2013-01-01' and project.status='closed')
                 or ( estimated_start_date<='2013-01-01' and project.status='open' and estimated_end_date>='2013-04-01')))
        then project.id -- here project id
        else null 
        end) as Number
from project
    inner join project_cstm 
        on project.id=project_cstm.id_c
    inner join accounts 
        on project_cstm.account_id_c=accounts.id
    inner join project_task 
        on project_task.project_id=project.id
where booked_date>'2013-01-01' 
and booked_date<'2015-01-01'
group by accounts.name

Upvotes: 2

fthiella
fthiella

Reputation: 49079

I couldn't test your query, but I think you should use something like this:

select accounts.name, count(DISTINCT project.id) as Number
from project   
  inner join project_cstm on project.id=project_cstm.id_c
  inner join accounts on project_cstm.account_id_c=accounts.id
  inner join project_task on project_task.project_id=project.id
where
  (booked_date>'2013-01-01' and booked_date<'2015-01-01')
  AND ((estimated_start_date<='2013-01-01' and project.status='closed')
      OR
       (estimated_start_date<='2013-01-01'
        and project.status='open'
        and estimated_end_date>='2013-04-01'))
group by accounts.name

Upvotes: 0

Related Questions