如何编写SQL 2005查询 [英] how to write query for sql 2005

问题描述

我有一个表结构为

I have a table structure as

Amt     Name    TransType
20	abc	buy
10	abc	buy
15	abc	buy
8	abc	sell
2	abc	sell

我想编写一个查询，将结果集设置为

TotalAmt |名称
45 abc
-10 abc
35 GrandAbc

我已经尝试过但没有得到.

I want to write a query which give me result set as

TotalAmt | Name
45 abc
-10 abc
35 GrandAbc

I have tried but not getting it.

select  case when trantype='buy' then sum(amt) when trantype='sell' then ((-1)*sum(amt)) end as Total,Name
  from stock group by trantype
union all
select -----
but it is not giving the output as above

推荐答案

您正在汇总所有记录；您未指定何时添加或取消练习

试试这个:

You are summing ALL of the records; you are not specifying when to add or subract

Try this:

SELECT 
SUM(
CASE Transtype
	WHEN 'Buy' THEN amt
	WHEN 'Sell' THEN (amt*-1)
END) 
 AS Total
FROM #tbl

尝试一下:

Try this:

DECLARE @BOUGHT INT, @SOLD INT

SET @BOUGHT = (SELECT SUM(amt) FROM tableAbc WHERE transtype = 'buy')
SET @SOLD = (SELECT SUM(amt) FROM tableAbc WHERE transtype = 'sell')

SELECT @BOUGHT - @SOLD

这篇关于如何编写SQL 2005查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！