CODEWITHSUNDEEP
c#.netasp.netsqlsql-server
hesam salehnamadi
hesam salehnamadi

Reputation: 199

how to get sum of this column in sql server

i'm use below query for fetch my data with an ordernumber...

SELECT     Details.Quantity, 
           Details.OrderNumber, 
           Orders.OrderDate, 
           Products.ProductName, 
           Products.UnitPrice ,
           Details.Quantity*Products.UnitPrice as qprice
FROM       Details 
INNER JOIN Orders 
   ON Details.OrderNumber = Orders.OrderNumber 
INNER JOIN Products 
   ON Details.ProductID = Products.ProductID
where Orders.OrderNumber='14195'

this query give me below result

enter image description here

now i want give sum of qprice column..

how can i do this??

Upvotes: 1

Views: 1741

Answers (1)

Alex Aza
Alex Aza

Reputation: 78447

Like this?

SELECT SUM(Details.Quantity * Products.UnitPrice) AS Amount
FROM Details 
    INNER JOIN Orders ON 
        Details.OrderNumber = Orders.OrderNumber
    INNER JOIN Products ON 
        Details.ProductID = Products.ProductID
WHERE Orders.OrderNumber = '14195'

If you need to have TotalAmount as an extra column to what you already have then:

SELECT    
    Details.Quantity, 
    Details.OrderNumber, 
    Orders.OrderDate,
    Products.ProductName,
    Products.UnitPrice,
    Details.Quantity*Products.UnitPrice as qprice,
    SUM(Details.Quantity*Products.UnitPrice) OVER() TotalAmount
FROM Details 
    INNER JOIN Orders ON 
        Details.OrderNumber = Orders.OrderNumber
    INNER JOIN Products ON 
        Details.ProductID = Products.ProductID
WHERE Orders.OrderNumber = '14195'

Upvotes: 4

Related Questions