LOOKER: Count for a subset of columns

[Former Community Member]

Given 5 columns, I need to have a count of rows matching based on 2 columns. For example:

A	B	C	D	E	countAB
A	B	C	D	E	3
A	Z	F	X	Z	2
A	Z	Y	U	O	2
A	B	Q	R	T	3
A	B	Y	R	T	3

I need 6th column: countAB which count  total times this combination shows up in the table results.

In sql I could make a cte with A,B, count(*) and then join the result. I have to do this for a lot of different A,B,C combos so making a pdt for each or precalculating all counts in 1 pdt might hit on performance.

Any ideas on how to approach this?

[Former Community Member]

for future:

5k row limit applies to table calcs and merges. Merge query with separate query for A,B,count(*) was the solution. 5k on join does mean that sometimes result is null.

partition and rownum: https://www.googlecloudcommunity.com/gc/Technical-Tips-Tricks/How-do-I-do-a-ROW-NUMBER-OVER-PARTITIO...

max ts for partition*: https://www.googlecloudcommunity.com/gc/Modeling/Max-Date-Time-from-Partitioned-by-Client/td-p/57891....