Oracle count by condition



  • Good day, everyone! I've been dealing with a problem like that. There's a request that's coming back.

    | SOT  | KUT  | K1 | K2 | YADRO | GR  |
    |------|------|----|----|-------|-----|
    | 2055 | 1,31 | 0  | 3  | 2     | 101 |
    | 2055 | 1,31 | 0  | 3  | 2     | 101 |
    | 2055 | 1,31 | 0  | 3  | 2     | 103 |
    | 2055 | 1,31 | 1  | 0  | 1     | 100 |
    | 2055 | 1,31 | 0  | 3  | 3     | 102 |
    | 2055 | 1,31 | 0  | 3  | 3     | 102 |
    

    Where columns K1 and K2 show how many different groups (GR) belonging to the nucleus (YADRO) 1 visit the staff member (SOT), K2 shows how many different groups (GR) not belonging to the YADRO 1 visit the staff member (SOT). The kernels contain only unique groups, two different panel cores do not repeat. Request

    select sot,
    round(sum(time)over(partition by sot)/25920,2) as Кутил,
    case when yadro=2 then count(distinct gr)over(partition by sot,case when yadro=2 then 0 else 1 end) else 0 end as K1,
    case when yadro!=2 then count(distinct gr)over(partition by sot,case when yadro!=2 then 0 else 1 end) else 0 end as K2,
    

    ... from ...

    The problem is, the request goes back with zeros and needs it to be.

    | SOT   | KUT   | K1    | K2    | YADRO     | GR    |
    |------ |------ |---- |---- |------- |----- |
    | 2055 | 1,31 | 1 | 3 | 2 | 101 |
    | 2055 | 1,31 | 1 | 3 | 2 | 101 |
    | 2055 | 1,31 | 1 | 3 | 2 | 103 |
    | 2055 | 1,31 | 1 | 3 | 1 | 100 |
    | 2055 | 1,31 | 1 | 3 | 3 | 102 |
    | 2055 | 1,31 | 1 | 3 | 3 | 102 |

    If there's no zero, but there's a total number in all the columns, I'll be very grateful for the help, I'm just saying, you know, I'd like to do it without asking for extra questions, etc.



  • I'm not sure, but we should try this:

    count(distinct case when yadro=2 then gr else null end )over(partition by sot) as K1
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2