How can i join a table with another, then count not empty columns and group them by two other fields?

Question

(I have some issues adding the tables, as they are viewed as code. Have added the tags as code to include it)

I have a table with many columns (in example only a few)

month	col1	col2	col3	col4	col5
2021.06		87	987	987	87
2021.06		86		09	65
2021.06				09	65
2021.06				09
2021.05	85			09	65
2021.05	85			09
2021.05	87			09

I also have a second table with additional information connected to the id-number in the above table:

id	branch	info1	info2
85	branch1	test4	test5
86	branch1	test3
87	branch2	test2
987		test1
09	branch3	test1
65	branch1	test1

I need to find a simple way of joining the information, count the number of not empty columns and group it by the month and the branch. The result should look like this

month	branch	col1	col2	col3	col4	col5
2021.05	branch1	2	0	0	0	1
2021.05	branch2	1	0	0	0	0
2021.05	branch3	0	0	0	3	0
2021.06	branch1	0	1	0	0	2
2021.06	branch2	0	1	0	0	1
2021.06	branch3	0	0	0	3	0
2021.06		0	0	1	1	0

I have tried with join and union all, but the query is getting extremely large.

For some reason the tables cannot be added. Error say it is code but it isn't. Added it as code now — Levi H., Jul 07 '21 at 15:00
Added it correctly now. Found out I needed to add a space into empty cells in table for them not to be considered code :-) — Levi H., Jul 07 '21 at 15:12

score 1 · Accepted Answer · answered Jul 07 '21 at 15:17

Consider below approach

select * from (
  select month, branch, id, col
  from table1 
  unpivot (id for col in (col1,col2,col3,col4,col5))
  left join table2 using(id)
)
pivot (count(id) for col in ('col1','col2','col3','col4','col5'))
# order by month, branch nulls last

If applied to sample data in your question - output is

How can i join a table with another, then count not empty columns and group them by two other fields?

1 Answers1