CODEWITHSUNDEEP
sqlunion
David
David

Reputation: 15

SQL - same tables different where clause

Im new to sql and am trying to do the following:

my query currently pulls back two fields based on a where clause:

select distinct 
     count(distinct t1.p_id) "c1",
     count(distinct t2.sa_id) "c2" 
from capd_section t5, 
     capd_department t6,
     capd_person t1,
     capd_studentapplication t2,
     capd_module t4,
     capd_moduleapplication t3 
where (t3.ma_studentapplication(+)=t2.sa_id) and 
      (t3.ma_module=t4.m_id(+)) and 
      (t4.m_modulesection=t5.s_id(+)) and 
      (t4.m_moduledept=t6.d_id(+)) and 
      (t4.m_reference not like '%%FTA%%') and 
      **(t2.sa_reference like '212%%')** and 
      (t4.m_reference not like '%%HE%%') and 
      (t4.m_reference not like '%%PT%%') and 
      (t4.m_name not like 'NCTJ%%') and 
      (t4.m_reference not like 'ME%%') and 
      (t2.sa_student=t1.p_id) 
having (count(distinct t3.ma_id)>0)

i am wanting to have the same query but with the where clause (t2.sa_reference like '213%%') to pull back aswell. (current Year & Previous Year)

so four fields in total (c1,c2,c3,c4). If that makes any sense at all. Is it even possible?

many thanks for any help :)

Upvotes: 1

Views: 340

Answers (2)

valex
valex

Reputation: 24134

Just use CASE in the COUNT statement:

select distinct 
     count(distinct t1.p_id) "c1",
     count(distinct t2.sa_id) "c2",
     count(distinct case when t2.sa_reference like '212%%' then t1.p_id else null end) "c3",
     count(distinct case when t2.sa_reference like '212%%' then t2.sa_id else null end) "c4"

from capd_section t5, 
     capd_department t6,
     capd_person t1,
     capd_studentapplication t2,
     capd_module t4,
     capd_moduleapplication t3 
where (t3.ma_studentapplication(+)=t2.sa_id) and 
      (t3.ma_module=t4.m_id(+)) and 
      (t4.m_modulesection=t5.s_id(+)) and 
      (t4.m_moduledept=t6.d_id(+)) and 
      (t4.m_reference not like '%%FTA%%') and 
      (t4.m_reference not like '%%HE%%') and 
      (t4.m_reference not like '%%PT%%') and 
      (t4.m_name not like 'NCTJ%%') and 
      (t4.m_reference not like 'ME%%') and 
      (t2.sa_student=t1.p_id) 
having (count(distinct t3.ma_id)>0)

Upvotes: 0

Ajo Koshy
Ajo Koshy

Reputation: 1225

You can simply add an or statement to check for the second value and try your query as : 

select distinct 
     count(distinct t1.p_id) "c1",
     count(distinct t2.sa_id) "c2" 
from capd_section t5, 
     capd_department t6,
     capd_person t1,
     capd_studentapplication t2,
     capd_module t4,
     capd_moduleapplication t3 
where (t3.ma_studentapplication(+)=t2.sa_id) and 
      (t3.ma_module=t4.m_id(+)) and 
      (t4.m_modulesection=t5.s_id(+)) and 
      (t4.m_moduledept=t6.d_id(+)) and 
      (t4.m_reference not like '%%FTA%%') and 
      ((t2.sa_reference like '212%%')or (t2.sa_reference like '213%%')) and 
      (t4.m_reference not like '%%HE%%') and 
      (t4.m_reference not like '%%PT%%') and 
      (t4.m_name not like 'NCTJ%%') and 
      (t4.m_reference not like 'ME%%') and 
      (t2.sa_student=t1.p_id) 
having (count(distinct t3.ma_id)>0);

Here I have modified your condition to check for both values by using the clause as ((t2.sa_reference like '212%%')or (t2.sa_reference like '213%%')). So if either is fulfilled you can retrieve the rows.

Upvotes: 1

Related Questions