Distinct Count Help

[gtb12314]

Hello All,

Below is my command, I want to know how to have the distinct count of per_end_date for each pay_sum_grp(i.e; for FLP,PTS &PTU) for each employee.

please see the last two lines of my command to have an idea, please help me out.


WITH
eelist AS
(
SELECT assoc.company, assoc.process_level,assoc.deptname,assoc.department,assoc.emp_status,assoc.fte_total,assoc.employee,assoc.first_name as empfirst_name,assoc.last_name as emplast_name,assoc.middle_init as empmiddle_int,assoc.sup_fname,assoc.sup_lname,sup_middle_init,assoc.contract_manager
FROM lrsuser.v_associatelist assoc
WHERE
group_name='G:ACTIVE' AND emp_status IN ('CF','E9','EF','N9','NF','CA','EA','NA','CB','EB','NB')
),
eelistwithpto AS(

SELECT eelist.*, NVL2(ptoelig.elig_balance,ptoelig.elig_balance,0) As EligibleBal
from eelist left outer join (select assoc.company,assoc.employee, SUM(emtamastr.elig_balance) elig_balance
FROM eelist assoc inner join
lawson.emtamastr emtamastr
ON assoc.company = emtamastr.company
AND assoc.employee = emtamastr.employee
inner join lawson.planmaster planmaster
ON emtamastr.company = planmaster.company
AND emtamastr.plan_name = planmaster.plan_name
WHERE planmaster.end_eff_date = TO_DATE ('1700/01/01', 'YYYY/MM/DD')
AND planmaster.plan_class in ('PTO', 'VAC')
group by assoc.company, assoc.employee
) ptoelig
on eelist.company = ptoelig.company
and eelist.employee = ptoelig.employee
)
select eelistwithpto.*, prt.hours,prt.per_end_date, prt.pay_sum_grp
FROM eelistwithpto INNER JOIN lawson.prtime prt
ON eelistwithpto.company=prt.company
AND eelistwithpto.employee=prt.employee
WHERE prt.pay_sum_grp IN ('FLP','PTS','PTU')
AND TO_CHAR(prt.per_end_date,'YYYY')='2007'

Thanks!!!

 

[SantaMufasa]

GTB,

The problem with DISTINCT is that once you use the feature, then it causes a DISTINCT operation on all fields in combination, not individually. Therefore, I suggest that if you want distinct COUNTs on certain columns, that you do so using in-line views that produce counts by either [ul][li]"COUNT(DISTINCT <column>)" or[/li][li]"COUNT <column>...WHERE <condition>"[/li][/ul]Let us know which method you choose and how it works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

You could do this with a windowing function. See the example below:

select y, count(distinct x) over (partition by y)
from
(select 1 as x, 1 as y
from dual
union all
select 2 as x, 1 as y
from dual
union all
select 2 as x, 1 as y
from dual
union all
select 5 as x, 2 as y
from dual)