0

I'm trying to count the records of each period for specific employees. I already removed unnecessary records by using "row_number() over (partition by...", then in the select, above I counted and grouped them to show how many times they exist for a given period number. The output and my SQL looks like this:

SELECT TBL.Name, TBL.Period_ID, count(*) as PeriodCount from (
Select row_number() over (partition by TBL1.Period_Id, TBL1.Name, TBL1.Date order by 
TBL1.Period_Id desc) as rn1,  * from ( 

<selected columns>
...
<joins>
...
<where clause>
...

) as TBL1
)as TBL
where TBL.rn1 = 1 

Group by TBL.Name, TBL.Period_ID
order by 1

Output:

enter image description here

But what I actually want is to show all the periods for all users even with 0 as counted values. The desired output should look like:

enter image description here

I've already tried using the solution with left join

How to include "zero" / "0" results in COUNT aggregate?

after ")TBL" section but results stay the same.

Can you help me with this, please? :)

Bart

Seyedraouf Modarresi
  • 1,213
  • 9
  • 14
Alwexistwo
  • 37
  • 1
  • 7

2 Answers2

0

You need cross join :

with cte as (
     <your query>
)
select distinct c.name, c1.period_id, coalesce(cc.period_count, 0) as period_count
from cte c cross join
     ( select distinct c1.period_id from cte c1 
     ) c1 left join
     cte cc
     on cc.name = c.name and cc.period_id = c1.period_id;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
0

You use a cross join to general the rows -- all combinations of names and periods. Then a left join to bring in the current information:

with cte as (
     <your query>
     )
select n.name, p.period_id, coalesce(cte.period_count, 0) as period_count
from (select distinct name from cte) n cross join
     (select distinct period_id from cte) p left join 
     cte 
     on cte.name = n.name and cte.period_id = p.period_id;

This uses select distinct to get the desired names and periods. If you have another source (even a derived table of constants), then that is usually a better choice.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Do I need to change anything in my query that i just posted above and use your solution? Because sadly the results didnt changed... – Alwexistwo Apr 11 '20 at 11:46
  • @Alwexistwo. . . Yes, you need to put your existing query inside `cte`. – Yogesh Sharma Apr 11 '20 at 11:47
  • I already did that. The results still not showing the 0... Is it because the rows with period number 115 and 116 doesnt exist for user3? But even so, I wanted to show them as 0... – Alwexistwo Apr 11 '20 at 11:49