CODEWITHSUNDEEP
sqlsql-serverdatabase
Barış Velioğlu
Barış Velioğlu

Reputation: 5817

Joining Two Tables in SQL Server

These two SQL statements give me the results shown below. I need to concatenate them by grouping for MerchantId, BatchId and Currency

I need a new table that contains all these columns

MerchantId - BatchId - T1 - T2- NotSold - Sold- Currency

Queries:

select 
   MerchantId as MerchantId, 
   BatchId as BatchId, 
   COUNT(BatchId)as T1, SUM(Amount) as NotSold, 
   Currency as Currency  
from 
   [Order]
where 
   OrderStatus = 4 and MerchantId = 1
group by 
   BatchId, Currency,MerchantId

select 
   MerchantId as MerchantId, 
   BatchId as BatchId, 
   COUNT(BatchId) as T2, 
   SUM(Amount) as Sold, 
   Currency 
from 
   [Order]
where 
   OrderStatus = 1 and MerchantId = 1 
group by 
   BatchId, Currency,MerchantId

Upvotes: 0

Views: 822

Answers (3)

sgeddes
sgeddes

Reputation: 62841

Assuming the Currency is the same in both tables, and you want to combine your above queries, something like this (untested) should work:

select O.MerchantId ,
   O.BatchId , 
   COUNT(O.BatchId)as T1, 
   SUM(O.Amount) as NotSold , 
   COUNT(O2.BatchId) as T2, 
   SUM(O2.Amount) as Sold , 
   O.Currency 
from [Order] O
   LEFT JOIN [Order] O2 ON O.MerchantId = O2.MerchantId 
          AND O.BatchId = O2.BatchId 
          AND O.Currency = O2.Currency 
          AND O2.OrderStatus = 1 and O2.MerchantId = 1 
where O.OrderStatus = 4 and O.MerchantId = 1
group by O.BatchId, O.Currency, O.MerchantId

Upvotes: 0

andrew.butkus
andrew.butkus

Reputation: 777

SELECT *
FROM t1, t2
LEFT JOIN t1.MerchantId ON t2.MerchantId = t1.MerchantId

and then scale it up to perform on batchId, and select only the columns that you want as a result (rather than *)

Upvotes: 0

Taryn
Taryn

Reputation: 247690

You will want to use the aggregate function with a CASE expression:

select MerchantId as MerchantId, 
  BatchId as BatchId, 
  count(case when OrderStatus = 4 then BatchId end) T1,
  count(case when OrderStatus = 1 then BatchId end) T2,
  sum(case when OrderStatus = 4 then Amount else 0 end) NotSold,
  sum(case when OrderStatus = 1 then Amount else 0 end) Sold,
  Currency as Currency  
from [Order] 
where MerchantId = 1
group by BatchId, Currency, MerchantId

See SQL Fiddle with Demo

Using your sample data the result is:

| MERCHANTID | BATCHID | T1 | T2 | NOTSOLD | SOLD | CURRENCY |
--------------------------------------------------------------
|          1 |       1 |  1 |  1 |      11 |   11 |       TR |
|          1 |       2 |  0 |  1 |       0 |   11 |       TR |
|          1 |       3 |  1 |  1 |      11 |   11 |       TR |
|          1 |       4 |  2 |  1 |      22 |   11 |       TR |
|          1 |       1 |  2 |  2 |      22 |   22 |      USD |
|          1 |       2 |  2 |  1 |      22 |   11 |      USD |
|          1 |       4 |  0 |  1 |       0 |   11 |      USD |

Upvotes: 2

Related Questions