Noob - Needs SUM(Fresheyes) to help find an issue

1

This is my first attempt at a Union and I am trying to sort a query by orderID and then the last line will show the orderID Total. I am not sure why, but my SUM does not appear to be working right. Could one of you seasoned veterans do me justice and help me understand what is right and what is wrong with my query. I appreciate any help you could offer.

Here is my Query:

SELECT 
CAST(Orders.OrderID AS VARCHAR), 
Orders.ShipCompanyName, 
Orders.ShipFirstName, 
Orders.ShipLastName, 
Orders.Shipaddress1, 
Orders.ShipAddress2, 
Orders.ShipCity, 
Orders.ShipState, 
Orders.ShipPostalCode, 
Orders.ShipCountry, 
OrderDetails.ProductCode, 
OrderDetails.ProductPrice, 
OrderDetails.Quantity 

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID 

WHERE (OrderDetails.ProductPrice <> 0) AND (Orders.SalesTax1 <> 0) AND (OrderDetails.Quantity <> 0) AND (Orders.ShipDate NOT LIKE 'Null') AND (month(Orders.ShipDate)=5) AND (year(Orders.ShipDate)=2016) 

GROUP BY Orders.OrderID, Orders.ShipCompanyName, Orders.ShipFirstName, Orders.ShipLastName, Orders.Shipaddress1, Orders.ShipAddress2, Orders.ShipCity, Orders.ShipState, Orders.ShipPostalCode, Orders.ShipCountry, OrderDetails.ProductCode, OrderDetails.ProductPrice, OrderDetails.Quantity 

UNION 

SELECT 
CAST(Orders.OrderID AS VARCHAR) + ' Total',  
' ' , 
' ', 
' ', 
' ', 
' ',  
' ', 
' ', 
' ', 
' ', 
' ',  
cast(sum(OrderDetails.ProductPrice) AS MONEY), 
' '  

FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID 

WHERE (OrderDetails.ProductPrice <> 0) AND (Orders.SalesTax1 <> 0) AND (OrderDetails.Quantity <> 0) AND (Orders.ShipDate NOT LIKE 'Null') AND (month(Orders.ShipDate)=5) AND (year(Orders.ShipDate)=2016) 

GROUP BY Orders.OrderID,  OrderDetails.ProductPrice 

ORDER BY CAST(Orders.OrderID AS VARCHAR

Here is a .jpg of the first few lines of results:

)

queryResult.jpg1625×357 115 KB

As you can see from the results. The Total line is not a single total of all OrderIDs and in some examples it actually adds and totals, but in others it just carries the line down to the total line.

Thank you in advance for any help.

2

In the second part of UNION, you are doing grouping by also OrderDetails.ProductPrice.
This is why your total line is generated X times.
(GROUP BY Orders.OrderID, OrderDetails.ProductPrice)

Try to group only by OrderID

1 Like
3

First this probably should be done with a front end or reporting service.

You do not need the GROUP BY in the top part of the UNION and the GROUP BY in the bottom should be

GROUP BY CAST(Orders.OrderID AS VARCHAR) + ' Total'

Give the calculated column an alias say CharOrderID then you that in your ORDER BY.

1 Like
4

Thank you both!

5

Could you explain what you mean by a front end or reporting service?

6

A front end would be the calling program say a .NET program.
For reporting service, SQL Server has a reporting service, that I am aware of but do not use, so I do not know how to get started there without looking it up.

7

I see. I am using a sql query builder that comes with an ecommerce package. The only way I can export queries is either XML or CSV. The people I create the queries for just open them in Excel. I could pretty them up in excel before sending them, but that would take too much time. I would love to be able to run the query in PHP and let HTML do the formatting but the ecommerce provider does not allow accessing the database outside the platform.