HELP with a MSSQL statement Select Query (Put a SUM in the last line)

How do I make the query sum all of the data from the final "LineTotal" column?

Here is the code as I have it:

SELECT Orders.CustomerID
     , Orders.OrderID
     , Orders.BatchNumber
     , Products_Joined.ProductCode
     , Products_Joined.ProductName
     , OrderDetails.ProductPrice
     , Products_Joined.Vendor_Price
     , OrderDetails.Quantity
     , (OrderDetails.Quantity * Products_Joined.Vendor_Price) AS LineTotal

FROM ((Products_Joined WITH (NOLOCK) INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode) 
     INNER JOIN Orders 
     ON OrderDetails.OrderID = Orders.OrderID) 
          INNER JOIN Customers 
          ON Orders.CustomerID = Customers.CustomerID 
WHERE Orders.BatchNumber = 5304

Open in new window


I just need to be able to see all of the "LineTotals" added up with the value on the final line. Is that possible?
LVL 1
greghollAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Are you looking for running sum? Because you already have LineTotal at the end. Is it not working?
0
Ryan ChongCommented:
try

SELECT Orders.CustomerID , Orders.OrderID , Orders.BatchNumber , Products_Joined.ProductCode , Products_Joined.ProductName , OrderDetails.ProductPrice , Products_Joined.Vendor_Price , OrderDetails.Quantity , sum(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS LineTotal FROM ((Products_Joined WITH (NOLOCK) INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode) INNER JOIN Orders ON OrderDetails.OrderID = Orders.OrderID) INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Orders.BatchNumber = 5304
group by
Orders.CustomerID , Orders.OrderID , Orders.BatchNumber , Products_Joined.ProductCode , Products_Joined.ProductName , OrderDetails.ProductPrice , Products_Joined.Vendor_Price , OrderDetails.Quantity
0
greghollAuthor Commented:
Thank you eghtebas and Ryan Chong

First of all, eghtebas: Indeed a running total would work too. The Line Totals work just fine.  But what I'm looking for is the GRAND total at the bottom (a sum of all the line totals).

Ryan Chong: Thank you for your help. However, I'm not getting a total. I'm attaching a csv file of the query output as you have copied above.

Thanks guys
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan ChongCommented:
can you show us the output you want?
0
Mike EghtebasDatabase and Application DeveloperCommented:
Please, first try the following to see if it produces the same results as you have now before we move forward:
;With cte
As
(
SELECT Orders.CustomerID
     , Orders.OrderID
     , Orders.BatchNumber
     , Products_Joined.ProductCode
     , Products_Joined.ProductName
     , OrderDetails.ProductPrice
     , Products_Joined.Vendor_Price
     , OrderDetails.Quantity
     , (OrderDetails.Quantity * Products_Joined.Vendor_Price) AS LineTotal

FROM ((Products_Joined WITH (NOLOCK) INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode) 
     INNER JOIN Orders 
     ON OrderDetails.OrderID = Orders.OrderID) 
          INNER JOIN Customers 
          ON Orders.CustomerID = Customers.CustomerID 
WHERE Orders.BatchNumber = 5304
) 
Select * From cte;

Open in new window

What version of SQL Server are you working with? If the above works, then try:
;With cte
As
(
SELECT Orders.CustomerID
     , Orders.OrderID
     , Orders.BatchNumber
     , Products_Joined.ProductCode
     , Products_Joined.ProductName
     , OrderDetails.ProductPrice
     , Products_Joined.Vendor_Price
     , OrderDetails.Quantity
     , (OrderDetails.Quantity * Products_Joined.Vendor_Price) AS LineTotal

FROM ((Products_Joined WITH (NOLOCK) INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode) 
     INNER JOIN Orders 
     ON OrderDetails.OrderID = Orders.OrderID) 
          INNER JOIN Customers 
          ON Orders.CustomerID = Customers.CustomerID 
WHERE Orders.BatchNumber = 5304
) 
Select CustomerID, OrderID, BatchNumber, ProductCode, ProductName, ProductPrice, Vendor_Price, Quantity, LineTotal From cte
Union
Select null,       null   , null       , null       , null       , null        , null        , null    , sum(LineTotal) From cte;

Open in new window

0
greghollAuthor Commented:
Sorry,  I was away for a couple days.

As mentioned in the question title, I'm using MS SQL.

I got this error for both of the above examples:

Your SQL is invalid: Command text was not set for the command object.
0
Ryan ChongCommented:
>>Your SQL is invalid: Command text was not set for the command object.
can you post the codes relating to the Command object?
0
greghollAuthor Commented:
Ryan: I copied and pasted exactly as eghtebas has above. Both examples of eghtebas give the same error
0
Ryan ChongCommented:
do you have some sample data can share and show us how the output will look like?
0
greghollAuthor Commented:
Here is my code (a little revised from what I had above):

SELECT
      Products_Joined.warehousecustom AS Arancel
    , Orders.BatchNumber                AS Remesa
    , Products_Joined.WarehouseBin AS ProductoSimple 
    , Products_Joined.Vendor_Price   AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.BatchNumber = 5309
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price
;

Open in new window


Attached is the output. There are two files.
1. OutPut.csv - this is the output as the query is for now
OutPut.csv

2. OutPut-revised.csv - this is how I would like the output to show. Notice this is an extra line at the bottom with the GRAND TOTALS?
OutPut-revised.csv

Seems pretty simple, eh?
0
Ryan ChongCommented:
think probably we need to do it via an Union clause, try this:

(code not tested)
SELECT 
      Products_Joined.warehousecustom AS Arancel
    , Orders.BatchNumber                AS Remesa
    , Products_Joined.WarehouseBin AS ProductoSimple 
    , Products_Joined.Vendor_Price   AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.BatchNumber = 5309
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price


union

SELECT
    'GRAND TOTALS' AS Arancel
    , '' AS Remesa
    , '' AS ProductoSimple 
    , '' AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.BatchNumber = 5309

ORDER BY CASE WHEN Remesa = '' THEN 99999 ELSE Remesa END, 1, 2

Open in new window

0
greghollAuthor Commented:
Looking great. But I got an error, namely:

Your SQL is invalid: Invalid column name 'Remesa'.
0
Ryan ChongCommented:
a quick fix would be as follows:

select * from
(
  SELECT 
      Products_Joined.warehousecustom AS Arancel
    , Orders.BatchNumber                AS Remesa
    , Products_Joined.WarehouseBin AS ProductoSimple 
    , Products_Joined.Vendor_Price   AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.BatchNumber = 5309
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price
    

union

SELECT
    'GRAND TOTALS' AS Arancel
    , '' AS Remesa
    , '' AS ProductoSimple 
    , '' AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.BatchNumber = 5309 
) a
ORDER BY CASE WHEN Remesa = '' THEN 99999 ELSE Remesa END, 1, 2

Open in new window

0
greghollAuthor Commented:
So far so good. Awesome Ryan. Thanks.
One final thing though: The sums come out nicely, however, the addition is not correct.  Attached is the output I got:
data-output.csv
Notice that the sums for columns E and F are incorrect?
Column E's sum shows 134. (The correct value should be 107)
Column F's sum shows 716.34. (The correct value should be 582)

- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – - - – – – – – – – – – – – – – – – – – – –
The following is off topic:

Furthermore, are you interested in me hiring you to do another tweak on this report that I have in mind? Or I can post it as a new question in Experts-Exchange.

The tweak is the following:
In the example output there are about 7 items called "FOLIO DE CUERO" all with different prices. Also there are 5 of "FOLIO DE COROSIL" of different prices. I would like to edit this report so that it summarizes the data. (i.e. I only want to see ONE item of "FOLIO DE COROSIL" and ONE item of "FOLIO DE CUERO" with an aggregated $ total ("totallinea") and aggregated quantity ("cantidad").) The column for unit price ("precio unitario") will be omitted from the final result.
- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – - - – – – – – – – – – – – – – – – – – – –
0
Ryan ChongCommented:
I didn't obtain your data so I can't really provide an optimized solution, but for a quick fix, you can try like this:

select * from
(
  SELECT 
      Products_Joined.warehousecustom AS Arancel
    , Orders.BatchNumber                AS Remesa
    , Products_Joined.WarehouseBin AS ProductoSimple 
    , Products_Joined.Vendor_Price   AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.BatchNumber = 5309
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price
    
union

select 
'GRAND TOTALS' AS Arancel, 
, '' AS Remesa
    , '' AS ProductoSimple 
    , '' AS PrecioUnitario
    , SUM(Cantidad)      AS Cantidad
    , SUM(TotalLinea) AS TotalLinea
(
SELECT 
      Products_Joined.warehousecustom AS Arancel
    , Orders.BatchNumber                AS Remesa
    , Products_Joined.WarehouseBin AS ProductoSimple 
    , Products_Joined.Vendor_Price   AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.BatchNumber = 5309
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price
) x

) a
ORDER BY CASE WHEN Remesa = '' THEN 99999 ELSE Remesa END, 1, 2

Open in new window


for your additional question, you probably need to join more tables using additional UNION clause, then in your original SQL scripts above you probably need to exclude those records with productosimple in "FOLIO DE CUERO" and "FOLIO DE COROSIL"
0
greghollAuthor Commented:
I get the following error:

Your SQL is invalid: Incorrect syntax near ','.
0
Ryan ChongCommented:
ops try:

select * from
(
  SELECT 
      Products_Joined.warehousecustom AS Arancel
    , Orders.BatchNumber                AS Remesa
    , Products_Joined.WarehouseBin AS ProductoSimple 
    , Products_Joined.Vendor_Price   AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.BatchNumber = 5309
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price
    
union

select 
'GRAND TOTALS' AS Arancel
, '' AS Remesa
    , '' AS ProductoSimple 
    , '' AS PrecioUnitario
    , SUM(Cantidad)      AS Cantidad
    , SUM(TotalLinea) AS TotalLinea
(
SELECT 
      Products_Joined.warehousecustom AS Arancel
    , Orders.BatchNumber                AS Remesa
    , Products_Joined.WarehouseBin AS ProductoSimple 
    , Products_Joined.Vendor_Price   AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.BatchNumber = 5309
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price
) x

) a
ORDER BY CASE WHEN Remesa = '' THEN 99999 ELSE Remesa END, 1, 2
 

Open in new window

0
greghollAuthor Commented:
Almost there. Oh dear. Now I've got:

Your SQL is invalid: Incorrect syntax near '('.
0
Ryan ChongCommented:
omg... try this again... hopefully it works now

select * from
(
  SELECT 
      Products_Joined.warehousecustom AS Arancel
    , Orders.BatchNumber                AS Remesa
    , Products_Joined.WarehouseBin AS ProductoSimple 
    , Products_Joined.Vendor_Price   AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.BatchNumber = 5309
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price
    
union

select 
'GRAND TOTALS' AS Arancel
, '' AS Remesa
    , '' AS ProductoSimple 
    , '' AS PrecioUnitario
    , SUM(Cantidad)      AS Cantidad
    , SUM(TotalLinea) AS TotalLinea
from 
(
SELECT 
      Products_Joined.warehousecustom AS Arancel
    , Orders.BatchNumber                AS Remesa
    , Products_Joined.WarehouseBin AS ProductoSimple 
    , Products_Joined.Vendor_Price   AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
WHERE Orders.BatchNumber = 5309
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price
) x

) a
ORDER BY CASE WHEN Remesa = '' THEN 99999 ELSE Remesa END, 1, 2
 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
greghollAuthor Commented:
Weird. The query gives no errors. There are sums for the 'cantidad' and 'totallinea', however, the sums are INCORRECT. Please check out the attached csv file.  :S
sample-output.csv
0
Ryan ChongCommented:
it's very weird since the "grand total" is derived from the "same" SQL statement.

here's the sample data I have tested, which looks ok for me:

create table Products_Joined
(
  ProductCode int,
  ProductName varchar(50),
  warehousecustom varchar(30),
  WarehouseBin varchar(30),
  Vendor_Price money
);
Create table Orders
(
  OrderID int,
  BatchNumber int
);
Create table OrderDetails
(
  OrderID int,
  ProductCode int,
  Quantity int
);

insert into Products_Joined(ProductCode,ProductName,warehousecustom,WarehouseBin,Vendor_Price)values(1,'ProductName1','4202.32.00.00','bin',120);
insert into Products_Joined(ProductCode,ProductName,warehousecustom,WarehouseBin,Vendor_Price)values(2,'ProductName2','4202.32.11.00','bin',10);
insert into Products_Joined(ProductCode,ProductName,warehousecustom,WarehouseBin,Vendor_Price)values(3,'ProductName3','4202.32.00.22','bin',50);

insert into Orders(OrderID,BatchNumber)values(1,5309);
insert into Orders(OrderID,BatchNumber)values(2,5310);
insert into Orders(OrderID,BatchNumber)values(3,5311);

insert into OrderDetails(OrderID,ProductCode,Quantity)values(1,2,33);
insert into OrderDetails(OrderID,ProductCode,Quantity)values(1,1,10);
insert into OrderDetails(OrderID,ProductCode,Quantity)values(1,3,22);
insert into OrderDetails(OrderID,ProductCode,Quantity)values(2,2,60);
insert into OrderDetails(OrderID,ProductCode,Quantity)values(2,3,40);
insert into OrderDetails(OrderID,ProductCode,Quantity)values(3,2,100);
insert into OrderDetails(OrderID,ProductCode,Quantity)values(3,1,35);

select * from
(
  SELECT 
      Products_Joined.warehousecustom AS Arancel
    , Orders.BatchNumber                AS Remesa
    , Products_Joined.WarehouseBin AS ProductoSimple 
    , Products_Joined.Vendor_Price   AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
-- WHERE Orders.BatchNumber = 5309
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price
    
union

select 
'GRAND TOTALS' AS Arancel
, '' AS Remesa
    , '' AS ProductoSimple 
    , '' AS PrecioUnitario
    , SUM(Cantidad)      AS Cantidad
    , SUM(TotalLinea) AS TotalLinea
from 
(
SELECT 
      Products_Joined.warehousecustom AS Arancel
    , Orders.BatchNumber                AS Remesa
    , Products_Joined.WarehouseBin AS ProductoSimple 
    , Products_Joined.Vendor_Price   AS PrecioUnitario
    , SUM(OrderDetails.Quantity)      AS Cantidad
    , SUM(OrderDetails.Quantity * Products_Joined.Vendor_Price) AS TotalLinea
FROM Products_Joined WITH (NOLOCK)
    INNER JOIN OrderDetails ON Products_Joined.ProductCode = OrderDetails.ProductCode
    INNER JOIN Orders       ON OrderDetails.OrderID = Orders.OrderID
-- WHERE Orders.BatchNumber = 5309
GROUP BY
      Orders.BatchNumber
    , Products_Joined.ProductCode
    , Products_Joined.ProductName
    , Products_Joined.warehousecustom
    , Products_Joined.WarehouseBin 
    , Products_Joined.Vendor_Price
) x

) a
ORDER BY CASE WHEN Remesa = '' THEN 99999 ELSE Remesa END, 1, 2
 

Open in new window


can you post your version here for diagnosis?
0
greghollAuthor Commented:
Thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.