GROUP_BY WITH DISTINCT QUERY [英] GROUP_BY WITH DISTINCT QUERY

问题描述

  CENTER_ID UNIT EMPLOYEEID LESSON MINS_STUDIED SECTION 
 I234 4 G01234 4.1 5 EX 
 I234 4 G01234 4.1 5 LN 
 I234 4 G01234 4.1 5 VO 
 
 I234 4 G0123 4.2 5 EX 
 I234 4 G0123 4.2 5 LN 
 I234 4 G0123 4.2 5 VO 
 
 I2345 5 G023 5.2 12 EX 
 I2345 5 G023 5.2 12 LN 
 I2345 5 G023 5.2 12 VO 
  

从记录1到6，它包含2个不同的employee_id，他们研究了单元4.在这种情况下，我需要每单位花费的总分钟数（center_id wise ）。

但我想因此，第4单元的center_id I234的总分钟数是（5 + 5）= 10，而第5单元的center_id I2345的总分钟数是（5 + 5）= 10。 12。

我用下面的查询

  SELECT 
 COUNT（DISTINCT EMPLOYEE_ID），CENTER_ID，UNIT，
 SUM（DISTINCT MINS_STUDIED）
 FROM 
 TEST_DATA 
 GROUP BY 
 CENTER_ID，DBD.UNIT; 
  

以上查询返回

  center_id UNIT DISTINCT（EMPLOYEE_ID）TOTAL_MINS_STUDIED 
 I234 4 2 5 
 I2345 5 1 12 
  

但是，我需要如下输出：

  center_id UNIT DISTINCT（EMPLOYEE_ID）TOTAL_MINS_STUDIED 
 I234 4 2 10 
 I2345 5 1 12 
  

解决方案

如果我们假设你只有两个employeeID，最后一个输入是一个错字。

计算总和。

  SELECT center_ID，unit，count（EmployeeID）cnt，sum（hours_Studied）sumHrs 
 FROM（SELECT TD 
 GROUP BY CENTER_ID，单位
   FROM test_data > 
 
 
给我们： I234,4,2,10  
 
 
 
或使用分析：虽然我不知道如何处理hours_studied总结，如果他们非常通过employeeID ...所以排序可能是不正确的。
  SELECT Center_ID，Unit，count（Distinct EmployeeID）cnt_Dist_Emp，sum（Hours_Studied）sum_hrs 
 FROM（select row_number（）over（PARTITION BY center_ID，unit，employeeID，Hours_Studied order by center_ID，unit ，employeeID，Hours_Studied）rn，TD。* 
 FROM Test_data td）
 WHERE RN = 1 
 GROUP BY Center_ID，Unit 
  
 
Am using Oracle 12c, below is the column:
CENTER_ID     UNIT      EMPLOYEEID    LESSON  MINS_STUDIED  SECTION
I234          4         G01234         4.1        5            EX
I234          4         G01234         4.1        5            LN
I234          4         G01234         4.1        5            VO

I234          4         G0123          4.2        5            EX
I234          4         G0123          4.2        5            LN
I234          4         G0123          4.2        5            VO

I2345         5         G023           5.2       12            EX
I2345         5         G023           5.2       12            LN
I2345         5         G023           5.2       12            VO
From record 1 to 6, it contains 2 distinct employee_id who studied unit 4. In this case, I need total minutes spent per unit (center_id wise).

But I want to add only distinct MINS_STUDIED based on EMPLOYEE_ID.

So, total minutes spent for center_id I234 for unit 4 is (5+5)=10 and for center_id I2345 for unit 5 is 12.

I used the below query
    SELECT 
         COUNT(DISTINCT EMPLOYEE_ID),CENTER_ID,UNIT,
         SUM(DISTINCT MINS_STUDIED)
    FROM 
         TEST_DATA
    GROUP BY 
          CENTER_ID,DBD.UNIT;
The above query returns
center_id    UNIT  DISTINCT(EMPLOYEE_ID) TOTAL_MINS_STUDIED
I234          4      2                       5 
I2345         5      1                       12 
But, I need the output like below:
center_id    UNIT  DISTINCT(EMPLOYEE_ID) TOTAL_MINS_STUDIED
I234          4      2                       10 
I2345         5      1                       12

解决方案
If we assume you only have two employeeID's and the last entry is a typo..

Simply get a distinct list before you calculate totals.
SELECT center_ID, unit, count(EmployeeID) cnt, sum(hours_Studied) sumHrs
FROM (SELECT distinct Center_ID, Unit, EmployeeID, hours_Studied 
      FROM test_data) TD
GROUP BY CENTER_ID, unit
Giving us: I234, 4, 2, 10 

or using an analytic:  though I'm not sure how to handle what hours_studied to sum if they very by employeeID... so the ordering may be incorrect.
SELECT Center_ID, Unit, count(Distinct EmployeeID) cnt_Dist_Emp , sum(Hours_Studied) sum_hrs
FROM (Select row_number() over (PARTITION BY center_ID, unit, employeeID, Hours_Studied order by center_ID, unit, employeeID, Hours_Studied ) rn, TD.*
      FROM Test_data td)
WHERE RN = 1 
GROUP BY Center_ID, Unit


                        
这篇关于GROUP_BY WITH DISTINCT QUERY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！