Home » SQL & PL/SQL » SQL & PL/SQL » Help required for taking distinct count without using DISTINCT keyword
Help required for taking distinct count without using DISTINCT keyword [message #668719] |
Mon, 12 March 2018 11:47 |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
effective_date employee_id dept_type dept_code India Japan
31-dec-2017 123 Tech dept1 Y
31-dec-2017 87 Tech dept1 Y
31-dec-2017 87 Tech dept2 Y Y
31-dec-2017 87 Tech dept3 Y
31-dec-2017 192 Tech dept1 Y
31-dec-2017 192 Tech dept2 Y Y
31-dec-2017 18 HR dept1 Y
31-dec-2017 145 Accnt dept2
My output result should be
effective_date dept_type dept_code india_count japan_count asia_count total_emp
31-dec-2017 Tech dept1 1 2 3 5
31-dec-2017 Tech dept2 2 1 2 5
31-dec-2017 Tech dept3 1 0 1 5
31-dec-2017 Hr dept1 1 0 1 5
31-dec-2017 Acct dept2 0 0 0 5
From the above output total_emp & asia_count should be based on distinct employee_ids
I have to create a View.
I have tried below. But not working
x
with A as (select effective_date,dept_type,dept_code,india,japan
from emp
group by effective_date,dept_type,dept_code,india,japan),
select effective_date,dept_type,dept_code,count(india),count(japan),count(india)+count(Japan) as Asia
from A group by effective_date,dept_type,dept_code
The count should be different based on the filter condition passed.
In case if i pass the effective_date as 31-dec-2017 & dept_type as Tech then my total_emp should be 3.
It should be of distinct employee_id's count. But i have to take the count without using distinct.
|
|
|
|
Re: Help required for taking distinct count without using DISTINCT keyword [message #668723 is a reply to message #668722] |
Mon, 12 March 2018 11:58 |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
shared the create and insert script
create table emp(effective_date date,employee_id varchar2(10),dept_type varchar2(50),dept_code varchar2(20),india varchar2(1),japan varchar2(1))
insert into emp values('31-dec-2017','123','Tech','dept1','Y',null);
insert into emp values('31-dec-2017','87','Tech','dept1',null,'Y');
insert into emp values('31-dec-2017','87','Tech','dept2','Y','Y');
insert into emp values('31-dec-2017','87','Tech','dept3','Y',null);
insert into emp values('31-dec-2017','192','Tech','dept1',null,'Y');
insert into emp values('31-dec-2017','192','Tech','dept2','Y','Y');
insert into emp values('31-dec-2017','18','Hr','dept1','Y',null);
insert into emp values('31-dec-2017','145','Accnt','dept2',null,null);
|
|
|
|
Re: Help required for taking distinct count without using DISTINCT keyword [message #668728 is a reply to message #668726] |
Mon, 12 March 2018 18:31 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
EdStevens wrote on Mon, 12 March 2018 14:30What is the meaning of the INDIA and JAPAN columns in table EMP?
Why do you have multiple occurrences of the same EMPLOYEE_ID, all with the same EFFECTIVE_DATE?
What does EFFECTIVE_DATE indicate?
This looks to me like fatal design flaw in that your table(s) are not designed to Third Normal Form.
And by what rule to you determine if something is included in ASIA_COUNT in your desired output? What column/value in EMP determines if a row should be counted as ASIA or "not asia"?
see end of line below
select effective_date,dept_type,dept_code,count(india),count(japan),count(india)+count(Japan) as Asia
from A group by effective_date,dept_type,dept_code
BTW, OP has an error in expected/desired result for TECH dept2. ASIA_COUNT should be 3; not 2.
|
|
|
Re: Help required for taking distinct count without using DISTINCT keyword [message #668729 is a reply to message #668723] |
Mon, 12 March 2018 18:36 |
Solomon Yakobson
Messages: 3273 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
with t as (
select e.*,
count(distinct employee_id) over() total_emp
from emp_tbl e
)
select effective_date,
dept_type,
dept_code,
count(india) india_count,
count(japan) japan_count,
count(india) + count(japan) asia_count,
total_emp
from t
group by effective_date,
dept_type,
dept_code,
total_emp
order by effective_date,
dept_type desc,
dept_code
/
EFFECTIVE DEPT_TYPE DEPT_CODE INDIA_COUNT JAPAN_COUNT ASIA_COUNT TOTAL_EMP
--------- ---------- ---------- ----------- ----------- ---------- ----------
31-DEC-17 Tech dept1 1 2 3 5
31-DEC-17 Tech dept2 2 2 4 5
31-DEC-17 Tech dept3 1 0 1 5
31-DEC-17 Hr dept1 1 0 1 5
31-DEC-17 Accnt dept2 0 0 0 5
SQL>
SY.
|
|
|
Re: Help required for taking distinct count without using DISTINCT keyword [message #668737 is a reply to message #668729] |
Tue, 13 March 2018 05:56 |
|
msol25
Messages: 396 Registered: June 2011
|
Senior Member |
|
|
hi,
Please use below query:
select effective_date
,dept_type
,dept_code
,count(case when india = 'Y' then 1 end) as india_count
,count(case when japan = 'Y' then 1 end ) as japan_count
,count(case when india = 'Y' or japan = 'Y' then 1 end) as asia_count
,(select count(employee_id)
from(
select employee_id,
count(1)
from emp
group by employee_id
)
) as total_count
from emp
group by effective_date
,dept_type
,dept_code;
|
|
|
|
Goto Forum:
Current Time: Thu May 09 18:06:48 CDT 2024
|