SQL to count occurrences

I want to build 4 extra columns (below right 4) to my table.

• Count the occurrences of "device+date"
• In separated columns which match with "source" value in the row

Here's the Excel formula for the last 2 columns, how can I write the logic in SQL?

S1_count_in_the_date:  =COUNTIFS(F:F,F2,A:A,"S1")    S2_count_in_the_date:  =COUNTIFS(F:F,F2,A:A,"S2")  

source	device	date	...	source+device+date	device+date	S1_count_in_the_date	S2_count_in_the_date
S1	A	2021-01-03	…	S1A2021-01-03	A2021-01-03	1	2
S2	A	2021-01-03	…	S2A2021-01-03	A2021-01-03	1	2
S2	A	2021-01-03	…	S2A2021-01-03	A2021-01-03	1	2

Thanks in advance

https://stackoverflow.com/questions/66774580/sql-to-count-occurrences March 24, 2021 at 11:59AM