CODEWITHSUNDEEP
sqlsql-serversql-server-2008join
user2516394
user2516394

Reputation: 81

Join multiple tables in Select query with aggregate functions

I want to join these two queries, I want to subtract SQuantity from PQuantity and SAmount from PAmount. How can I achieve this with one query.

SELECT        
SUM(QtyShare) AS SQuantity, SUM(QtyShare * Rate) AS SAmount,CompanyId
FROM Sales
WHERE (UserId = 1)
GROUP BY CompanyId

SELECT  SUM(Quantity) AS PQuantity, SUM(Quantity * Rate) AS PAmount,CompanyId
FROM (SELECT   UserId, CompanyId, Quantity, Rate AS Rate
       FROM  Purchase
       UNION ALL
       SELECT UserId, CompanyId, Quantity, 0 AS Rate
       FROM   Bonus
       UNION ALL
       SELECT UserId, CompanyId, Quantity, Rate AS Rate
       FROM   RightShare) AS t
WHERE (UserId = 1) GROUP BY CompanyId

Upvotes: 2

Views: 4096

Answers (1)

user359040
user359040

Reputation:

Try:

SELECT CompanyId,
       SUM(PQuantity) PQuantity, 
       SUM(PQuantity * PRate) PAmount,
       SUM(SQuantity) SQuantity, 
       SUM(SQuantity * sRate) SAmount
       SUM(PQuantity) - SUM(SQuantity) TQuantity, 
       SUM(PQuantity * PRate) - SUM(SQuantity * sRate) TAmount
FROM 
(SELECT UserId, CompanyId, Quantity PQuantity, Rate PRate, 0 SQuantity, 0 SRate
 FROM  Purchase
 UNION ALL
 SELECT UserId, CompanyId, Quantity PQuantity, 0 PRate, 0 SQuantity, 0 SRate
 FROM   Bonus
 UNION ALL
 SELECT UserId, CompanyId, Quantity PQuantity, Rate PRate, 0 SQuantity, 0 SRate
 FROM   RightShare
 UNION ALL
 SELECT UserId, CompanyId, 0 PQuantity, 0 PRate, QtyShare SQuantity, Rate SRate
 FROM   Sales) sq
WHERE (UserId = 1) 
GROUP BY CompanyId

Upvotes: 3

Related Questions