如何计算列值？

venkat 发表于 Dev

文卡特

这是我的查询：

SELECT DISTINCT r1.STATE_OFFICE_CODE,
   r1.AREA_OFFICE_CODE,
   r1.AREA_OFFICE_NAME,
   r2.LAST_EOD_EXECUTION
FROM report_one r1
INNER JOIN report2 r2 ON r1.distributor_code = r2.distributor_code
ORDER BY r1.AREA_OFFICE_CODE;

一切正常。输出：

但是，当我尝试计算列数时，r2.LAST_EOD_EXECUTION出现错误“不是按功能分组”：

SELECT DISTINCT r1.STATE_OFFICE_CODE,
   r1.AREA_OFFICE_CODE,
   r1.AREA_OFFICE_NAME,
   r2.LAST_EOD_EXECUTION,
   count(r2.LAST_EOD_EXECUTION) AS NumberOfDates
FROM report_one r1
INNER JOIN report2 r2 ON r1.distributor_code = r2.distributor_code
ORDER BY r1.AREA_OFFICE_CODE;

有人可以帮忙吗？如何计算值？

穆雷尼克

删除distinct关键字并添加一个group by子句：

SELECT r1.STATE_OFFICE_CODE,
   r1.AREA_OFFICE_CODE,
   r1.AREA_OFFICE_NAME,
   r2.LAST_EOD_EXECUTION,
   count(r2.LAST_EOD_EXECUTION) AS NumberOfDates
FROM report_one r1
INNER JOIN report2 r2 ON r1.distributor_code = r2.distributor_code
GROUP BY r1.STATE_OFFICE_CODE,
   r1.AREA_OFFICE_CODE,
   r1.AREA_OFFICE_NAME,
   r2.LAST_EOD_EXECUTION
ORDER BY r1.AREA_OFFICE_CODE;

编辑：
尚不清楚您要实现的目标，但是如果您要获取LAST_EOD_EXECUTION每个组的不同值的数量，则需要在：中使用distinct关键字count：

SELECT r1.STATE_OFFICE_CODE,
   r1.AREA_OFFICE_CODE,
   r1.AREA_OFFICE_NAME,
   r2.LAST_EOD_EXECUTION,
   count(distinct r2.LAST_EOD_EXECUTION) AS NumberOfDates
FROM report_one r1
INNER JOIN report2 r2 ON r1.distributor_code = r2.distributor_code
GROUP BY r1.STATE_OFFICE_CODE,
   r1.AREA_OFFICE_CODE,
   r1.AREA_OFFICE_NAME,
   r2.LAST_EOD_EXECUTION
ORDER BY r1.AREA_OFFICE_CODE;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。