SQL Query Help SUM of Columns accross multiple tables

sqlsum

Hi I wonder if you can help with the following query , I am going around in circles trying to get the syntax correct.

I have two Tables Orders

OrderID | Product ID | LineTotal
   1         ABC          2
   2         CDE          3
   2         DEF          1

and Products Table Containing the Weight and Cost

ProductID | Weight | Cost 
   ABC        1        1
   CDE        2        2  
   DEF        1        0.5

So for each order ID I need to SUM the LineTotal the Weight and the Cost.

Thanks for some pointers on how to go about this as I am getting errors with joins and silly results

Thanks

Best Answer

It should be very simple if I got the task right:

SELECT o.OrderID, o.ProductID, sum = (o.LineTotal + p.Weight + p.Cost) 
FROM ORDERS o
INNER JOIN PRODUCTS p on o.ProductID = p.ProductID

Related Solutions

SQL MAX of multiple columns

Here is another nice solution for the Max functionality using T-SQL and SQL Server

SELECT [Other Fields],
  (SELECT Max(v) 
   FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]

Values is the Table Value Constructor.

"Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified either as the VALUES clause of an INSERT ... VALUES statement, or as a derived table in either the USING clause of the MERGE statement or the FROM clause."

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Best Answer

Related Solutions

SQL MAX of multiple columns

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

Related Topic