根据条件求和列 [英] sum columns based on condition

问题描述

你好朋友

我正在尝试编写一个从vb.net运行ms访问表的查询。我有一个包含三列col1，col2和col3的表。在col1中，不超过两行具有相同的字母。我想知道如何根据第1列减去列？根据条件求和列我可以使用这样的查询

hello friends
I am trying to write a query to run in ms access table from vb.net. I have a table with three columns col1,col2 and col3. there are no more than two rows with the same letter in col1.I would like to know how do I subtract columns based on column 1 ? for summing column based on condition I can use a query like this

select col1, SUM(col2) as col2, SUM(col3) as col3 from table1 group by col1 

< br $>




table1

col1 | col2 | col3 |

---- - | --- | ---- |

A | 10 | 12 |

B | 4 | 6 |

A | 7 | 2 |



输出

col1 | col2 | col3

---- | ---- | ---- |

A | 3 | 10 |

B | 4 | 6 |



由查询定义的SELECT列表中的别名'col2'引起的循环引用。

table1
col1|col2|col3|
------|---|----|
A |10 | 12 |
B | 4 | 6 |
A | 7 | 2 |

output
col1|col2 |col3
----|----|----|
A | 3 | 10 |
B | 4 | 6 |

Circular reference caused by alias 'col2' in query definition's SELECT list.

"select col1, MAX(col2) *2 - SUM(col2) as col2,MAX(col3) * 2 - SUM(col3) as col3   from table1 group by col1"

谢谢

Thanks

推荐答案

你可以使用子查询来解决它：

You can workaround it by using subquery:

SELECT col1, MAX(col2)*2-col2 as col2, MAX(col3)*2-col3 as col3
FROM ( 
    select col1, SUM(col2) as col2, SUM(col3) as col3
    from table1
    group by col1 
) AS T
GROUP BY col1;

这篇关于根据条件求和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！