2
votes

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).

Sample Table

2
Which dbms??? SQL is just a language. - Eric
Have added a sample data table. The software I use currently supports SQL SELECT queries written in ANSI, Oracle, SQL Server, IBM DB2, MySQL, Sybase, Informix and PostgreSQL SQL dialects. but apparently recommended to use the ANSI SQL dialect - Lou

2 Answers

1
votes

You can easily achieve this by using window functions in a case stmt:

SELECT         
    so.SalesOrderNumber as 'Sales Order Number',
    CASE WHEN LAG(so.id) OVER (PARITION BY so.salesOrderNumber ORDER BY so.id) IS NULL --this row is first in group, order however you determine "First"
        THEN soi.SubTotal
        ELSE NULL
    END AS subTotal
FROM  
    "Sales Order Items" AS soi LEFT JOIN 
    "Sales Orders" AS so ON so.salesOrderId = soi.salesOrderId 
0
votes

To create an additional row with the sum

  1. Duplicate the query
  2. Group the query by order
  3. Sum the line value column
  4. Use a Union to add this new query to the original.
  5. Add an additional column to both, hard code the first to 'lines' and second to 'order'

You can add a total column

  1. Duplicate the query
  2. Group the query by order
  3. Sum the line value column
  4. Put the new query in the from clause of the original, give it an alias
  5. Join the query by order number
  6. Add the sum column of the new query to the select clause of the first query
  7. Optionally, you can use use case statements to only show it for one line.