Select 좀 도와 주세요.
2개 테이블이 있습니다. (Bank_A, Bank_B)
2 개 테이블의 내용을 합쳐 놓으면 아래 처럼 보일 겁니다.
이것도 힘드네요.
Customer | Amount_A | Amount_B |
A | 20 |
|
A | 30 |
|
A |
| 10 |
B |
| 25 |
B |
| 20 |
C | 20 |
|
Customer 별로 Sum 을 나타내는 Select 를 하려고 합니다.
Customer | Amount_A | Amount_B |
A | 50 | 10 |
B |
| 45 |
C | 20 |
|
select Customer, sum(Amt_A) as Amount_A, sum(Amt_B) as Amount_B from
(
select Customer, Sum(Amount) as Amt_A from Bank_A
group by Customer
Union All
select Merchant as Customer, Sum(Amount) as Amt_B from Bank_B
group by Merchant
) as x
group by Customer
이렇게 해 보지만 Amt_B 에서 error 가 나네요.
고수님 도와 주세요.
아래는 위 select 를 위한 것입니다.
CREATE TABLE [dbo].[Bank_A](
[Customer] [nvarchar](10) NULL,
[Amount] [real] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Bank_B](
[Merchant] [nvarchar](10) NULL,
[Amount] [real] NULL
) ON [PRIMARY]
--INSERT INTO [TestDB].[dbo].[Bank_A] ([Customer],[Amount]) VALUES ('A',20)
--INSERT INTO [TestDB].[dbo].[Bank_A] ([Customer],[Amount]) VALUES ('A',30)
--INSERT INTO [TestDB].[dbo].[Bank_A] ([Customer],[Amount]) VALUES ('C',20)
--INSERT INTO [TestDB].[dbo].[Bank_B] ([Customer],[Amount]) VALUES ('A',10)
--INSERT INTO [TestDB].[dbo].[Bank_B] ([Customer],[Amount]) VALUES ('B',25)
--INSERT INTO [TestDB].[dbo].[Bank_B] ([Customer],[Amount]) VALUES ('B',20)
Comment 1
-
Hisory
2014.10.31 08:54
With CTE01 As
(
SElect [Customer],[Amount] As AmountA , 0 As AmountB from [Bank_a]
union all
SElect [Merchant],0 , [Amount] from [Bank_B]
)
Select [Customer] , SUM(AmountA) , SUM(AmountB)
from CTE01
group by [Customer]