Good day to all! Faced such a problem, there is a request that returns

| 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 are calculated, K1 shows how many different groups (GR) belong to the core (YADRO) 1 employee visits (SOT), K2 shows how many different groups (GR) does not belong to the core (YADRO) 1 visits employee (SOT). Kernels contain only unique groups; groups do not repeat in two different kernels. 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 that the query returns values ​​with zeros and it is necessary that it be something like this

 | 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 |

So that there are no zeros but there are common numbers in all columns, I will be very grateful for the help, I immediately say that wrappers should be offered only as a last resort, I would like to do without unnecessary subqueries, etc.

  • And why do we need case when yadro=2 then it returns 0 where yadro<>2 - Batanichek

1 answer 1

Not sure, but worth a try like this:

 count(distinct case when yadro=2 then gr else null end )over(partition by sot) as K1
  • Thank you so much kind man! Exactly what is needed! The most interesting thing is that I tried to do a similar request, although I put other conditions in over there and forgot to prescribe a distinct and therefore did not issue as necessary. Thank you in general! - VNprk