eqinna eqinna - 3 months ago 7
SQL Question

Subquery to sum values from a column

I use this code to sum an order for a certain date:

select od.OrderID, (od.unitprice * quantity) - ((od.unitprice * od.quantity) * discount) + freight as OrderSum
from orders, [Order Details] as od
where orderdate = '1996-12-12' and od.orderid = orders.orderid


The code above gives the following output table:

OrderID | OrderSum
10380 370,37
10380 506,63
10381 119,99


Desired end result and output: I want to sum the values in the created column "OrderSum" above so that the output would be put in a column named "Total ordersum". In other words, the desired output table I actually want:

Total ordersum
996,99


How can I add a subquery to the code above to solve this? I would be very thankful if someone could give a code sample.

Answer

Just do an aggregation query:

select sum(od.unitprice * quantity) - sum((od.unitprice * od.quantity) * discount) + sum(freight) as Total_OrderSum
from orders o join
     [Order Details] as od
     on od.orderid = o.orderid
where orderdate = '1996-12-12'; 

Note: Never use commas in the FROM clause. Always use explicit JOIN syntax with the conditions in the ON clause.