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 Go to next message
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 #668722 is a reply to message #668719] Mon, 12 March 2018 11:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But i have to take the count without using distinct.
WHY?

post CREATE TABLE & INSERT statements for sample data above
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 Go to previous messageGo to next message
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 #668726 is a reply to message #668723] Mon, 12 March 2018 16:30 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
What 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"?


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 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
EdStevens wrote on Mon, 12 March 2018 14:30
What 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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
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;

Re: Help required for taking distinct count without using DISTINCT keyword [message #668767 is a reply to message #668728] Tue, 13 March 2018 13:11 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
BlackSwan - ah, I overlooked the "count(case when india = 'Y' or japan = 'Y' then 1 end) as asia_count".

But that is yet another flaw, as I'm sure you'll agree that hard-coding like that is not flexible or scalable. You'd have to modify the code every time you add another country - in addition to modifying the table structure itself.
Previous Topic: Import data from excel to oracle Db
Next Topic: How Can I Execute Table Name in Order without using ORDER BY Clayuse
Goto Forum:
  


Current Time: Thu Mar 28 11:33:47 CDT 2024