How do I determine when I can include a partitioned field in a query that also includes non-partitioned fields that are grouped? I am getting no errors in my first query and cannot get the second one to run in bigquery.

Query1 below allows for the partition, and does not require that it be included in the group by. It runs just fine. (The create table is not associated with the issue I’m having).

Query2 errors out. If I don’t include the partition field in the group by it throws a “missing group by” error. BUT if I DO include the partition field in the group by, it throws the error “contains an analytic function, which is not allowed in GROUP BY”

QUERY1
create table bpsand1.test1 as
select balor, member_key, calendar_month_number, programyear, count(distinct z.calendar_month_number) over(partition by member_key, programyear) dms, sum(z.sales) sls from bpsand1.Net_Lift_Prog_YearX z
where z.ProgramYear in (2022, 2023)
group by 1,2, 3,4;

QUERY2
select dms, balor, programyear, calendar_month_number, dms
,count(distinct tbl.member_key) over(partition by dms) dmcounts
, count(member_key) counts, sum(sls) sls from bpsand1.test1 tbl
group by 1,2,3,4,5
order by 1,2,3,4,5

I tried both including and excluding the partitioned field in Query2 and neither work.