I have two tables:

I need to find the product name that was sold the most and the earnings from that.

The code I wrote:

SELECT * 
FROM Products 
WHERE ProductId = (SELECT ProductId
                   FROM 
                       (SELECT 
                            ProductId, 
                            SUM(Quantity) AS total_order,
                            MAX(SUM(Quantity)) OVER () AS maxSm 
                        FROM
                            Orders
                        GROUP BY
                            ProductId)
                   WHERE 
                        total_order = maxSm)

But with this I just find the product name that was sold the most. Can you tell me please how can I find the earnings only from this product?

CodePudding user response：

select top 1
    a.name,
    (b.total * a.price) as revenue
from
    products a
    left join (select productid, sum(quantity) as total group by productid) b
        on a.productid = b.productid
order by
    b.total desc

CodePudding user response：

You need to join the result of your derived table to your Products table.

Without actual sample data I am unable to test, however the following should be what you need, or at least very close:

select p.Name, o.total_order, o.total_order * p.Price as TotalValue
from (
    select * from (
        select ProductId, 
            Sum(Quantity) as total_order, 
            Max(Sum(Quantity)) over () as maxSm 
        from Orders
        group byProductId
    )t
    where total_order = maxSm
)o join Products p on p.ProductId=o.ProductId