用不同的名称和值的总和创建一个临时表 [英] Create a temp table with a distinct name and sum of values

问题描述

我有一张每位员工的费用表.该表可以有给定员工的多行，并且每行都有不同的成本.我想最后得到一个临时表，其中包含每位员工的总成本.所以:

I have a table of costs per employee. The table can have multiple rows for a given employee and in each row a distinct cost. I want to end up with a temp table that has the total costs for each employee. So:

Name | Cost
Dave | 563.22
John | 264.00

我尝试了以下操作，但以下内容对于更新费用无效.我有什么问题?还有没有更好的方法可以做到这一点.

I tried the following but the below is invalid for updating the cost. What do I have wrong? And is there a better way to do this.

declare @temp2  table
(
name text,
cost integer
)
insert @temp2(name) SELECT DISTINCT ename6 from dbo.condensed7day_query_result

UPDATE t  
SET t.cost = sum(dbo.condensed7day_query_result.cost)
FROM dbo.condensed7day_query_result
 WHERE dbo.condensed7day_query_result.ename6 = t.name)
FROM  @temp2 t

select * from @temp2

推荐答案

不要使用text数据类型.使用varchar().但是，您的问题的答案是一个聚合查询:

Don't use the text data type. Use varchar(). But the answer to your question is an aggregation query:

insert @temp2(name, cost) 
    select ename6, sum(dqr.cost)
    from dbo.condensed7day_query_result dqr
    group by dqr.ename6;

这篇关于用不同的名称和值的总和创建一个临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！