I am running a query on two tables that needs to return the total number of lines in each sales order and a total of the items ordered.

A simplified version of the SalesOrder table is constructed like this:

The second table, SalesOrderLine, contains the line items in the sales order:

The result of the query would look like this:

I am locking up on how to use the query to return the Total Number of Lines and Total Number of Items per SalesOrderID.

SELECT SalesOrder.SalesOrderID, SalesOrder.SODate, SalesOrder.SOCustomer

?? Total Number of Lines and Total Number of Items ??

FROM SalesOrder 
INNER JOIN SalesOrders ON SalesOrder.SalesOrderID = SalesOrderLine.SalesOrderID
ORDER BY SalesOrderID

CodePudding user response：

You can use apply :

select so.*, soo.*
from salesorder so cross apply
     ( select count(*) as Totallines, sum(soo.quantity) as TotalQty
       from SalesOrders soo
       where soo.SalesOrderID = so.SalesOrderID
     ) soo;

CodePudding user response：

You are almost done, except the aggregation.

Query

select so.SalesOrderID, so.SODate, so.SOCustomer, 
count(sol.LineNumber) as TotalLines, sum(sol.Quantity) as TotalItems
from SalesOrder as so
join SalesOrderLine as sol
on so.SalesOrderID = sol.SalesOrderID
group by so.SalesOrderID, so.SODate, so.SOCustomer
order by SalesOrderID;