使用uinon all进行sql查询 [英] sql query with uinon all

问题描述

我有一个表结构为

Hi,

I have a table structure as

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

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

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

但是它没有提供如上的输出.

But it is not giving the output as above.

推荐答案

我建​​议您使用over子句.该查询将类似于以下内容:

I would suggest you use over clause. The query will look something like this:

Select Name, Sum(Amt) Over (Partition by Name, transtype) as TransTypeTotal, 
Sum(Amt) as GrandTotal.

参见示例:
http://www.sqlteam.com/article/sql-sever- 2005-using-over-with-aggregate-functions [ ^ ]

See here an example:
http://www.sqlteam.com/article/sql-sever-2005-using-over-with-aggregate-functions[^]

尝试一下

Try this

IF OBJECT_ID( 'tempdb..#temp') IS NOT NULL
    BEGIN
    DROP TABLE #temp
    END
CREATE TABLE #temp
    (
    [Amt]       INT,
    [Name]      VARCHAR(50),
    [TransType] VARCHAR(50)
    )
INSERT INTO #temp( [Amt], [Name], [TransType])
SELECT 20,  'abc',  'buy' UNION
SELECT 10,  'abc',  'buy' UNION
SELECT 15,  'abc',  'buy' UNION
SELECT 8,   'abc',  'sell' UNION
SELECT 2,   'abc',  'sell'

SELECT  CASE WHEN [TransType]='buy' THEN SUM([Amt]) ELSE -1*SUM([Amt]) END AS [TotalAmt],
        [Name]
FROM    #temp
GROUP
BY      [Name],
        [TransType]

UNION ALL

SELECT  SUM( CASE WHEN [TransType]='buy' THEN [Amt] ELSE -1*[Amt] END) AS [TotalAmt],
        'Grand'+[Name] AS [Name]
FROM    #temp
GROUP
BY      [Name]

这篇关于使用uinon all进行sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！