ORA-00904:“ COUNT1”:无效的标识符00904。00000-“%s:无效的标识符” *原因:
*操作:
行错误:8列:73
SELECT a1.branch_name AS myfavourite,
COUNT(a1.branch_name) AS count1,
SUM(count1) AS total_purchase ,
a1.branch_id AS branch_id,
c1.city,
c1.Branch_description AS description,
c1.userid AS shopmailid,
c1.image
FROM tbl_orderdetails a1
INNER JOIN tbl_ordermaster b1
ON a1.order_master_id=b1.ordermasterid
INNER JOIN tbl_user c1
ON c1.id =a1.branch_id
WHERE b1.user_id='12'
GROUP BY a1.branch_name,
a1.branch_id,
c1.city,
c1.Branch_description,
c1.userid,
c1.image
ORDER BY COUNT(a1.branch_name) DESC
我想对count1求和并获得总购买价值。我遇到了类似上面的错误
该文档非常清楚,您不能像尝试使用列别名那样在同一选择列表中使用,因为只能在order by
:中使用它:
c_alias
为列表达式指定别名。Oracle数据库将在结果集的列标题中使用此别名。该AS
关键字是可选的。别名在查询期间有效地重命名了选择列表项。别名可以order_by_clause
在查询的其他子句中使用。
您可以使用内联视图(如Vignesh Kumar所示)或公用表表达式(如vinoth_S所示,称为CTE,也称为子查询分解)来解决该问题。但是,这两个答案仅显示您的total_purchase
值,而不显示count1
结果集中每一行的单独值。
您不能只删除别名并执行此操作,SUM(COUNT(a1.branch_name)) AS ...
因为这将给您带来ORA-00937错误-总计的分组不清楚。
您可以使用的解析版本SUM
一次计算两个值:
SELECT a1.branch_name AS myfavourite,
COUNT(a1.branch_name) AS count1,
SUM(COUNT(a1.branch_name)) OVER (PARTITION BY NULL) AS total_purchase ,
...
如果原始查询得到:
MYFAVOURITE COUNT1 BRANCH_ID CITY DESCRIPTION SHOPMAILID IMAGE
----------- ---------- ---------- ---------- ----------- ---------- ----------
BR2 12 12 Paris Branch 2 12 image 2
BR1 10 11 London Branch 1 12 image 1
BR3 1 13 New York Branch 4 12 image 3
...然后加上该分析总和将得出:
MYFAVOURITE COUNT1 TOTAL_PURCHASE BRANCH_ID CITY DESCRIPTION SHOPMAILID IMAGE
----------- ---------- -------------- ---------- ---------- ----------- ---------- ----------
BR2 12 23 12 Paris Branch 2 12 image 2
BR1 10 23 11 London Branch 1 12 image 1
BR3 1 23 13 New York Branch 4 12 image 3
...在结果集中的所有行中具有相同的总计数值,您在注释中说的就是您想要的。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句