I have a query table which includes data from several tables Joined with LEFT JOIN. So far everything works, with the resulting data being the individual items from Sales Orders. I can pull through the Total Sales Order Value into a column, but I only want this to be displayed on 1 of the rows for that Order OR into a separate row to represent the whole order.
My thoughts are that either I pull through the total but somehow only do this if it doesn't already appear for that order already OR I use SUM() but this seems to only work if used with GROUP BY and then I only get shown the totals and not the individual items.
I don't really know what to try as I don't even know if this is possible, and internet searches are not giving me any results that seem to help, they are all about Grouping.
SELECT
so."Sales Order#" as 'Sales Order Number',
soi."Sub Total" as 'Sub-Total'
FROM
"Sales Order Items" AS "soi"
LEFT JOIN
"Sales Orders" AS "so" ON so."Sales order ID" = soi."Sales order ID"
I would like to add a column 'TCV' which SUMS the 'Sub Total' values from all items that have the same Sales Order Number. But I only want this sub-total to show once per Order (not against every order item).