Zoho Analytics GROUP BY报错：按日期范围统计唯一ID的SQL问题

问题根源 #

你遇到的GROUP BY报错，核心原因有两个：

1. 原核心语句里的MONTH("Opportunities.Created Time")属于非聚合列，但未被加入GROUP BY子句，而Zoho Analytics的SQL严格要求：SELECT中所有非聚合字段必须出现在GROUP BY中。
2. 写法逻辑冲突——if(..., count(DISTINCT(...)), '0')把聚合函数嵌套在条件判断里，数据库无法区分“行级条件判断”和“分组聚合统计”，导致语法逻辑混乱。

解决方案 #

要统计上月创建的唯一Opportunity ID数量，直接用Zoho Analytics原生支持的count_if函数即可，它能在聚合时直接过滤符合条件的记录，完美适配GROUP BY场景。

修改后的完整查询如下：

SELECT
     "Opportunities.Entity",
     count(DISTINCT ("Opportunities.Id")) AS 'Total Opportunities',
     -- 替换原错误语句：用count_if统计上月创建的唯一ID数
     count_if(MONTH("Opportunities.Created Time") = Month(Now() - 1), DISTINCT "Opportunities.Id") AS 'Last Month Opportunities',
     count_if(("Opportunities.Stage" = 'Deal Won')
     AND    ("Opportunities.Deal Type" = 'New Deal')
     AND    (MONTH("Opportunities.Closing Date") = Month(Now()) - 1)) As 'Deal Won Last Month',
     ((count_if(("Opportunities.Stage" = 'Deal Won')
     AND    (MONTH("Opportunities.Closing Date") = Month(Now()) - 1)
     AND    ("Opportunities.Deal Type" = 'New Deal'))) / count_if(Month("Opportunities.Created Time") = (Month(Now()) -1))) * 100 AS "Conversion Ratio",
     sum_if(((MONTH("Opportunities.Closing Date")) = ((month(now())) -1))
     AND    ("Opportunities.Deal Type" = 'New Deal')
     AND    ("Opportunities.Stage" = 'Deal Won'), "Opportunities.Actual Booking Amount") AS "Actual Booking Amount",
     avg_if(((MONTH("Opportunities.Closing Date")) = ((month(now())) -1))
     AND    ("Opportunities.Deal Type" = 'New Deal'), "Opportunities.Actual Booking Amount") AS "Average Booking Amount",
     sum_if(("Opportunities.Stage" = 'Deal Won')
     AND    ("Opportunities.Deal Type" = 'New Deal')
     AND    (Month("Payment Collection Info (Clubbed Duplicate OPP ID).Collection Date") = ((month(now())) -1)), "Payment Collection Info (Clubbed Duplicate OPP ID).Collection Amount") AS 'Collection Amount',
     count_if(("Payment Collection Info (Clubbed Duplicate OPP ID).Collection Amount" > 0)
     AND    (Month("Payment Collection Info (Clubbed Duplicate OPP ID).Collection Date") = ((month(now())) -1)
     AND    ("Opportunities.Stage" = 'Deal Won')
     AND    ("Opportunities.Deal Type" = 'New Deal'))) AS "No of orders (collection)",
     sum_if((Month("Opportunities.Closing Date") = ((month(now())) -1))
     AND    ("Opportunities.Stage" = 'Deal Won')
     AND    ("Opportunities.Deal Type" = 'New Deal'), "Opportunities.Pending Collection") as 'Pending Collection'
FROM  "Opp+paymentinfo+users" 
GROUP BY  "Opportunities.Entity"

关键修改说明 #

1. 替换核心语句：用count_if(条件, DISTINCT 字段)直接实现“统计上月创建的唯一ID数”，既符合GROUP BY规则，又避免了逻辑嵌套冲突。
2. 保留原查询所有其他统计逻辑，仅修正报错的核心部分。

优化补充（可选） #

如果要避免跨年统计错误（比如1月统计去年12月时，月份数字不匹配），可以把月份判断改为基于完整自然月的范围：

count_if("Opportunities.Created Time" >= DATE_TRUNC('month', Now() - INTERVAL '1 month') 
         AND "Opportunities.Created Time" < DATE_TRUNC('month', Now()), 
         DISTINCT "Opportunities.Id") AS 'Last Month Opportunities'

这样能精准统计上一个自然月的所有记录，不会出现跨年月的统计偏差。

内容的提问来源于stack exchange，提问作者Srikanth