blocks|key|4955598|text|我想这就是你想要实现的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4955599|SELECT
++++*
FROM+(
++++SELECT
++++++++(SUM(totalSpend)+/+SUM(numVisits))+AS+totalSpend
++++++++,+(SUM(daysToTxn2+%2B+daysToTxn3+%2B+daysToTxn4+%2B+daysToTxn5+%2B+daysToTxn6+%2B+daysToTxn7+%2B+daysToTxn8+%2B+daysToTxn9+%2B+daysToTxn10)+/+9)+as+avgDaysToTxn
++++FROM
+++++++++dbo.ANTHEM_IDS_JR+
++++GROUP+BY
+++++++++(SUM(daysToTxn2+%2B+daysToTxn3+%2B+daysToTxn4+%2B+daysToTxn5+%2B+daysToTxn6+%2B+daysToTxn7+%2B+daysToTxn8+%2B+daysToTxn9+%2B+daysToTxn10)+/+9)
++++HAVING+SUM(numVisits)+>+0
)
ORDER+BY+totalSpend;|code-block|syntax|javascript|4955600|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I think this is what you are trying to accomplish...

<pre><code>SELECT
 *
FROM (
 SELECT
 (SUM(totalSpend) / SUM(numVisits)) AS totalSpend
 , (SUM(daysToTxn2 + daysToTxn3 + daysToTxn4 + daysToTxn5 + daysToTxn6 + daysToTxn7 + daysToTxn8 + daysToTxn9 + daysToTxn10) / 9) as avgDaysToTxn
 FROM
 dbo.ANTHEM_IDS_JR 
 GROUP BY
 (SUM(daysToTxn2 + daysToTxn3 + daysToTxn4 + daysToTxn5 + daysToTxn6 + daysToTxn7 + daysToTxn8 + daysToTxn9 + daysToTxn10) / 9)
 HAVING SUM(numVisits) &gt; 0
)
ORDER BY totalSpend;
</code></pre>

blocks|key|1187403|text|在大多数SQL中，GROUP+BY不识别带别名的列。因此，需要将SELECT中的公式原样复制到GROUP+BY中。例如：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1187404|SELECT+(+CASE
+++++++++WHEN+SUM(numVisits)+=+0+THEN+0
+++++++++ELSE+++++++++++++++++++++++++SUM(totalSpend)/SUM(numVisits)
+++++++++END
+++++++)+AS+totalSpend+,
+++++++(+(+SUM(daysToTxn2
+++++++++++++%2B+daysToTxn3
+++++++++++++%2B+daysToTxn4
+++++++++++++%2B+daysToTxn5
+++++++++++++%2B+daysToTxn6
+++++++++++++%2B+daysToTxn7
+++++++++++++%2B+daysToTxn8
+++++++++++++%2B+daysToTxn9
+++++++++++++%2B+daysToTxn10
+++++++++++++)
+++++++++)+/+9
+++++++)+as+avgDaysToTxn
FROM+dbo.ANTHEM_IDS_JR+
GROUP+BY+(+(+SUM(daysToTxn2
+++++++++++++%2B+daysToTxn3
+++++++++++++%2B+daysToTxn4
+++++++++++++%2B+daysToTxn5
+++++++++++++%2B+daysToTxn6
+++++++++++++%2B+daysToTxn7
+++++++++++++%2B+daysToTxn8
+++++++++++++%2B+daysToTxn9
+++++++++++++%2B+daysToTxn10
+++++++++++++)
+++++++++)+/+9
+++++++)
ORDER+BY+totalSpend|code-block|syntax|javascript|1187405|entityMap^0|9|8|W|6|1B|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

In most SQL, <code>GROUP BY</code> does not recognize aliased columns. Therefore you need to duplicate the formula as-is in the <code>SELECT</code> into the <code>GROUP BY</code>. e.g.

<pre><code>SELECT ( CASE
 WHEN SUM(numVisits) = 0 THEN 0
 ELSE SUM(totalSpend)/SUM(numVisits)
 END
 ) AS totalSpend ,
 ( ( SUM(daysToTxn2
 + daysToTxn3
 + daysToTxn4
 + daysToTxn5
 + daysToTxn6
 + daysToTxn7
 + daysToTxn8
 + daysToTxn9
 + daysToTxn10
 )
 ) / 9
 ) as avgDaysToTxn
FROM dbo.ANTHEM_IDS_JR 
GROUP BY ( ( SUM(daysToTxn2
 + daysToTxn3
 + daysToTxn4
 + daysToTxn5
 + daysToTxn6
 + daysToTxn7
 + daysToTxn8
 + daysToTxn9
 + daysToTxn10
 )
 ) / 9
 )
ORDER BY totalSpend
</code></pre>

blocks|key|3486288|text|在group+by子句中出现错误的原因有两个：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3486289|问题#1.错误的直接原因是在group+by子句中不能通过名称或序号位置引用结果集中新创建的列:您必须精确地复制表达式。SQL并不是一种简洁的语言。学会喜欢重复打字。|BOLD|3486290|问题#2.，你有一个更大的。您正在尝试根据已汇总的值将事物划分为组以进行汇总。像下面这样的东西(本质上就是你想要做的)是什么意思？|3486291|select+sum(x)+,+sum(y)
from+foo
group+by+sum(y)|code-block|syntax|javascript|3486292|您的组是如何定义的？你到底想要达到什么目的？|3486293|entityMap^0|1|8|0|0|5|E|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|W|8|@$9|X|A|Y|B|H]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|I|3|J|5|6|7|11|8|@]|D|@]|E|$]]|$1|K|3|L|5|M|7|12|8|@]|D|@]|E|$N|O]]|$1|P|3|Q|5|6|7|13|8|@]|D|@]|E|$]]|$1|R|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|S|$]]

The reason you get the error in your <code>group by</code> clause is two-fold:

Problem #1. The proximate cause of the error is that newly-created columns in the result set can't be referenced by name &mdash; or ordinal position, for that matter, as allowed by <code>order by</code> &mdash; in the <code>group by</code> clause: you have to replicate the expression exactly. SQL is not known for being a terse language. Learn to love repetitive typing.

Problem #2. You have a bigger though. You're trying to partition things into groups for summarization based on their already summarized values. What does something like the following (essentially what you're trying to do) mean?

<pre><code>select sum(x) , sum(y)
from foo
group by sum(y)
</code></pre>

How are your groups defined? What exactly are you trying to accomplish?

blocks|key|1181068|text|我认为你可以省去group+by|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1181069|SELECT+(CASE+WHEN+numVisits>+THEN+totalSpend+/+numVisits
++++++++END)+AS+totalSpend+,
+++++++((daysToTxn2+%2B+daysToTxn3+%2B+daysToTxn4+%2B+daysToTxn5+%2B+daysToTxn6+%2B+daysToTxn7+%2B+daysToTxn8+%2B+daysToTxn9+%2B+daysToTxn10)+/
++++++++((case+when+daysToTxn2+is+not+null+then+1+else+0+end)+%2B
+++++++++(case+when+daysToTxn3+is+not+null+then+1+else+0+end)+%2B
+++++++++(case+when+daysToTxn4+is+not+null+then+1+else+0+end)+%2B
+++++++++(case+when+daysToTxn5+is+not+null+then+1+else+0+end)+%2B
+++++++++(case+when+daysToTxn6+is+not+null+then+1+else+0+end)+%2B
+++++++++(case+when+daysToTxn7+is+not+null+then+1+else+0+end)+%2B
+++++++++(case+when+daysToTxn8+is+not+null+then+1+else+0+end)+%2B
+++++++++(case+when+daysToTxn9+is+not+null+then+1+else+0+end)+%2B
+++++++++(case+when+daysToTxn10+is+not+null+then+1+else+0+end)
++++++++)+as+avgDaysToTxn
FROM+dbo.ANTHEM_IDS_JR+
ORDER+BY+totalSpend|code-block|syntax|javascript|1181070|entityMap^0|8|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

I think you can just dispense with the <code>group by</code>:

<pre><code>SELECT (CASE WHEN numVisits&gt; THEN totalSpend / numVisits
 END) AS totalSpend ,
 ((daysToTxn2 + daysToTxn3 + daysToTxn4 + daysToTxn5 + daysToTxn6 + daysToTxn7 + daysToTxn8 + daysToTxn9 + daysToTxn10) /
 ((case when daysToTxn2 is not null then 1 else 0 end) +
 (case when daysToTxn3 is not null then 1 else 0 end) +
 (case when daysToTxn4 is not null then 1 else 0 end) +
 (case when daysToTxn5 is not null then 1 else 0 end) +
 (case when daysToTxn6 is not null then 1 else 0 end) +
 (case when daysToTxn7 is not null then 1 else 0 end) +
 (case when daysToTxn8 is not null then 1 else 0 end) +
 (case when daysToTxn9 is not null then 1 else 0 end) +
 (case when daysToTxn10 is not null then 1 else 0 end)
 ) as avgDaysToTxn
FROM dbo.ANTHEM_IDS_JR 
ORDER BY totalSpend
</code></pre>

blocks|key|1181080|text|这是SQL中的执行顺序|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1181081|FROM+clause
WHERE+clause
GROUP+BY+clause
HAVING+clause
SELECT+clause
ORDER+BY+clause+|code-block|syntax|javascript|1181082|这意味着SELECT子句中的别名在GROUP+BY子句中不可见。您可以使用select子句中创建的别名的唯一位置是ORDER+BY子句，因为它是在SELECT之后执行的。在其他任何地方，您都必须复制计算值的语句，或者使用原始列名(而不是别名)作为列名。|1181083|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This is the order of execution in SQL

<pre><code>FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause 
</code></pre>

This means that the alias you have in the SELECT clause is not visible in the GROUP BY clause.
The only place you can use the alias created in the SELECT clause is in the ORDER BY clause since it is executed after the select. Anywhere else you would have to duplicate your statement for computed values or use the original column name (not the alias) for column names.

blocks|key|3486404|text|对于您的需求，您不需要直接进行分组。相反，只需计算列，然后在外部查询中进行聚合和分组。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3486405|SELECT+SUM(totalSpend)+totalSpend,+avgDaysToTxn+FROM
(
SELECT+(+CASE
+++++++++WHEN+isnull(numVisits,0)+=+0+THEN+0
+++++++++ELSE+++++++++++++++++++++++++totalSpend/numVisits
+++++++++END
+++++++)+AS+totalSpend+,
+++++++(+(+daysToTxn2
+++++++++++++%2B+daysToTxn3
+++++++++++++%2B+daysToTxn4
+++++++++++++%2B+daysToTxn5
+++++++++++++%2B+daysToTxn6
+++++++++++++%2B+daysToTxn7
+++++++++++++%2B+daysToTxn8
+++++++++++++%2B+daysToTxn9
+++++++++++++%2B+daysToTxn10
+++++++++++++)
++++++++++/+9
+++++++)+as+avgDaysToTxn
FROM+dbo.ANTHEM_IDS_JR+
)+A
GROUP+BY+avgDaysToTxn+--+error+here:+"Invalid+column+name+'avgDaysToTxn'."
ORDER+BY+totalSpend|code-block|syntax|javascript|3486406|希望能有所帮助。|3486407|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

For your requirement, you don't need to do grouping in the first hand. Instead, just do the calculation of columns and then in outer query, do the aggregation and grouping.

<pre><code>SELECT SUM(totalSpend) totalSpend, avgDaysToTxn FROM
(
SELECT ( CASE
 WHEN isnull(numVisits,0) = 0 THEN 0
 ELSE totalSpend/numVisits
 END
 ) AS totalSpend ,
 ( ( daysToTxn2
 + daysToTxn3
 + daysToTxn4
 + daysToTxn5
 + daysToTxn6
 + daysToTxn7
 + daysToTxn8
 + daysToTxn9
 + daysToTxn10
 )
 / 9
 ) as avgDaysToTxn
FROM dbo.ANTHEM_IDS_JR 
) A
GROUP BY avgDaysToTxn -- error here: "Invalid column name 'avgDaysToTxn'."
ORDER BY totalSpend
</code></pre>

Hope it helps.

I'm trying to execute the following code:

<pre><code>SELECT ( CASE
 WHEN SUM(numVisits) = 0 THEN 0
 ELSE SUM(totalSpend)/SUM(numVisits)
 END
 ) AS totalSpend ,
 ( ( SUM(daysToTxn2
 + daysToTxn3
 + daysToTxn4
 + daysToTxn5
 + daysToTxn6
 + daysToTxn7
 + daysToTxn8
 + daysToTxn9
 + daysToTxn10
 )
 ) / 9
 ) as avgDaysToTxn
FROM dbo.ANTHEM_IDS_JR 
GROUP BY avgDaysToTxn -- error here: "Invalid column name 'avgDaysToTxn'."
ORDER BY totalSpend
;
</code></pre>

but I get an error, "Invalid column name 'avgDaysToTxn'." on the GROUP BY line.

Here's my incoming data:

<pre><code>daysToTxn2 daysToTxn3 daysToTxn4 daysToTxn5 daysToTxn6 daysToTxn7 daysToTxn8 daysToTxn9 daysToTxn10 numVisits totalSpend
130 142 218 309 427 495 598 694 827 104 363.23
716 779 0 0 0 12 23 0 0 11 256.45
13 63 191 201 289 292 310 476 492 25 456.34
7 15 27 63 109 203 245 256 274 43 789.03
</code></pre>

And I'd like my outgoing data to be just 2 columns. One headed with totalSpend. The other headed with avgDaysToTxn.

Could anybody help me get rid of this error while executing the code?

SQL Server: Invalid column name 'avgDaysToTxn' error

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在尝试执行以下代码：SELECT ( CASE         WHEN SUM(numVisits) = 0 THEN 0         ELSE                         SUM(totalSpend)/SUM(numVisits)         END       ) AS tota...

问SQL Server:无效的列名'avgDaysToTxn‘错误
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server:无效的列名'avgDaysToTxn‘错误EN