CODEWITHSUNDEEP
sqlms-accessgroup-bysubquery
AppsBoss7
AppsBoss7

Reputation: 35

How to get the sum of a subquery that returns sums?

I would like to get the sum of the "sums" in the following case. How can I do that? The following picture represents the table I receive after performing the code below. I'd like to receive only 2 records which would be:

Or Gazit | 052-3322445 | 36400

Dan Guy | 050-3023322 | 12000

An image representing the table I'm getting

SELECT Distinct(o1.FirstName & " " & o1.LastName) as [Full Name], o1.PhoneStart & "-" & 
o1.PhoneNumber as [Phone Number],
(SELECT SUM(OrderProducts.ClientPrice*OrderProducts.Quantity) FROM OrderProducts WHERE 
OrderProducts.Order = o2.ID AND o2.Client = o1.ID) AS [Order Price]

FROM Clients as o1 INNER JOIN (Orders as o2 INNER JOIN OrderProducts as o3 ON o2.ID = 
o3.Order) ON o1.ID = o2.Client
ORDER BY 3 DESC;

Thanks.

Upvotes: 0

Views: 41

Answers (1)

Isaac
Isaac

Reputation: 3363

Either I am misunderstanding your requirement or you are over-complicating this. Seems like you just need some aggregation (i.e. sum) with a group by clause. Does this work for you?

select o1.FirstName & " " & o1.LastName     as [Full Name]
     , o1.PhoneStart & "-" & o1.PhoneNumber as [Phone Number]
     , sum(o3.ClientPrice * o3.Quantity)    as [Order Price]
from Clients o1
inner join Orders o2        on o1.ID = o2.Client
inner join OrderProducts o3 on o2.ID = o3.Order
group by o1.FirstName
       , o1.LastName
       , o1.PhoneStart
       , o1.PhoneNumber
order by 3 desc

Upvotes: 2

Related Questions