Sum of all group 2

[Cpreston]

Hi

I have the following query which returns 6 results. But I only want it to return one row with the sum of the TotalLineVolumeM3
I have tried using SUM but it still displays all 6 rows. I would only expect 1 row with same information except the totallinevolumem3 being the total of the 6 rows in that column. I have also tried taking out the description column Thanks

I am currently getting this screen shot.

SELECT     dbo.OrderHeader.OrderNumber, dbo.OrderHeader.DateRequired, dbo.Customer.Name, dbo.OrderHeader.DeliveryAddress, 
                      LEFT(RIGHT(dbo.OrderHeader.DeliveryAddress, 8), 2) AS PC, dbo.OrderHeader.NoOfLines, dbo.OrderHeader.TotalVolume, SUM(dbo.OrderLine.TotalVolume) 
                      AS [TotalLine Volume M3], dbo.Product.Description, dbo.ProductGroup.Name AS Expr1
FROM         dbo.Product INNER JOIN
                      dbo.OrderLine ON dbo.Product.ProductID = dbo.OrderLine.ProductID INNER JOIN
                      dbo.OrderHeader INNER JOIN
                      dbo.Customer ON dbo.OrderHeader.CustomerID = dbo.Customer.CustomerID ON dbo.OrderLine.OrderID = dbo.OrderHeader.OrderID INNER JOIN
                      dbo.ProductGroup ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID
GROUP BY dbo.OrderHeader.OrderNumber, dbo.OrderHeader.DateRequired, dbo.Customer.Name, dbo.OrderHeader.DeliveryAddress, 
                      LEFT(RIGHT(dbo.OrderHeader.DeliveryAddress, 8), 2), dbo.OrderHeader.NoOfLines, dbo.OrderHeader.TotalVolume, dbo.Product.Description, dbo.ProductGroup.Name, 
                      dbo.OrderLine.TotalVolume
HAVING      (dbo.OrderHeader.OrderNumber = 3289248) AND (dbo.ProductGroup.Name IN ('mouldings', 'loose stock', 'Spindles', 'Newels', 'Handrail Kits', 'DeckAncillaries', 
                      'DeckPosts', 'MDF Loose', 'Length stocks', 'MDF architraves loose', 'MDF skirtings loose', 'MDF windowboard loose'))

 

[gmmastros]

You're grouping by dbo.Product.Description which is different for each row. The simplest solution would be to remove that column from the select clause and the group by clause.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Cpreston]

Hi

Thanks for the reply. I did try the Suggestion but still get all 6 rows

Code is like below now

SELECT     dbo.OrderHeader.OrderNumber, dbo.OrderHeader.DateRequired, dbo.Customer.Name, dbo.OrderHeader.DeliveryAddress, 
                      LEFT(RIGHT(dbo.OrderHeader.DeliveryAddress, 8), 2) AS PC, dbo.OrderHeader.NoOfLines, dbo.OrderHeader.TotalVolume, SUM(dbo.OrderLine.TotalVolume) 
                      AS [TotalLine Volume M3]
FROM         dbo.Product INNER JOIN
                      dbo.OrderLine ON dbo.Product.ProductID = dbo.OrderLine.ProductID INNER JOIN
                      dbo.OrderHeader INNER JOIN
                      dbo.Customer ON dbo.OrderHeader.CustomerID = dbo.Customer.CustomerID ON dbo.OrderLine.OrderID = dbo.OrderHeader.OrderID INNER JOIN
                      dbo.ProductGroup ON dbo.Product.ProductGroupID = dbo.ProductGroup.ProductGroupID
GROUP BY     dbo.OrderHeader.OrderNumber, dbo.OrderHeader.DateRequired, dbo.Customer.Name, dbo.OrderHeader.DeliveryAddress, 
                      LEFT(RIGHT(dbo.OrderHeader.DeliveryAddress, 8), 2), dbo.OrderHeader.NoOfLines, dbo.OrderHeader.TotalVolume, dbo.ProductGroup.Name, 
                      (dbo.OrderLine.TotalVolume)
HAVING      (dbo.OrderHeader.OrderNumber = 3289248) AND (dbo.ProductGroup.Name IN ('mouldings', 'loose stock', 'Spindles', 'Newels', 'Handrail Kits', 'DeckAncillaries', 
                      'DeckPosts', 'MDF Loose', 'Length stocks', 'MDF architraves loose', 'MDF skirtings loose', 'MDF windowboard loose'))

 

[Cpreston]

I have also tried WITH ROLLUP but this just brings in 7 rows with the last one being the total but I don't need to see the rest of them.

Any ideas anyone? Thanks

 

[Olaf Doschke]

Why not leave out GROUP BY overall? If you want a sum of all data, you don't group by at all. If you want one sum of partial data, you introduce a WHERE clause, but still no GROUP BY, if you GROUP BY something you're bound to get more than one group. Not grouoping is the simplest way to avoid groups, even when you're 100% sure the where condition only returns a single group. I like to repeat: The more columns you group by the more groups you get.

Bye, Olaf.

 

[DaveInIowa]

Now you're grouping by OrderLine.TotalVolume which probably has a unique value for each line of the order. You're also grouping by ProductGroup.Name which, in this case, appears to be the same for each line in this order but I wouldn't rely on that being the case for all orders. You need to remove both of these in the GROUP BY.

See if this works:

SELECT oh.OrderNumber,
	   oh.DateRequired,
	   c.Name,
	   oh.DeliveryAddress,
	   oh.DeliveryAddress PC,
	   oh.NoOfLines,
	   oh.TotalVolume,
	   tv.TotalLineVolume
  FROM OrderHeader oh
  JOIN Customer c
    ON oh.CustomerID = c.CustomerID

 OUTER
 APPLY (SELECT SUM(ol.TotalVolume) TotalLineVolume
		  FROM OrderLine ol
		  JOIN Product p
		    ON ol.ProductID = p.ProductID
		  JOIN ProductGroup pg
		    ON p.ProductGroupID = pg.ProductGroupID
		 WHERE ol.OrderID = oh.OrderID
			   pg.Name IN ('mouldings','loose stock','etc.')
	   ) tv

 WHERE oh.OrderNumber = 3289248

 

[Cpreston]

Hi

Many thanks for the replies. I have made some progress and will answer per the reply.

Firstly OlafDoschke I tried taking the Group By out completely (code below) but when I run it I get this error message "Column 'OrderHeader.OrderNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". So not sure how to get round this.

SELECT 
		              OrderHeader.OrderNumber, OrderHeader.DateRequired, Customer.Name, OrderHeader.DeliveryAddress, 
                      LEFT(RIGHT(OrderHeader.DeliveryAddress, 8), 2) AS PC, OrderHeader.NoOfLines, OrderHeader.TotalVolume, SUM(OrderLine.TotalVolume) 
                      AS [Loose m3], Product.Description, ProductGroup.Name AS Expr1
FROM         Product INNER JOIN
                      OrderLine ON Product.ProductID = OrderLine.ProductID INNER JOIN
                      OrderHeader INNER JOIN
                      Customer ON OrderHeader.CustomerID = Customer.CustomerID ON OrderLine.OrderID = OrderHeader.OrderID INNER JOIN
                      ProductGroup ON Product.ProductGroupID = ProductGroup.ProductGroupID

Where      (OrderHeader.OrderNumber = 3289248) AND (ProductGroup.Name IN ('mouldings', 'loose stock', 'Spindles', 'Newels', 'Handrail Kits', 'DeckAncillaries', 
                      'DeckPosts', 'MDF Loose', 'Length stocks', 'MDF architraves loose', 'MDF skirtings loose', 'MDF windowboard loose'))_

Reply to DaveInIowa. Thanks for the code, I adjusted the code as below and this brings in the results as I hoping, I am just testing the testing the results. I also adjusted the code so it brought in the post code digits from the delivery address. As I mentioned it appears to work so hopefully this will do the trick. Thanks again for the great replys.

SELECT oh.OrderNumber,
	   oh.DateRequired,
	   c.Name as cname,
	   oh.DeliveryAddress,
	   LEFT(RIGHT(OH.DeliveryAddress, 8),2) AS PC,
	   oh.NoOfLines,
	   oh.TotalVolume,
	   tv.TotalLineVolume
  FROM OrderHeader oh
  JOIN Customer c
    ON oh.CustomerID = c.CustomerID

 OUTER
 APPLY (SELECT SUM(ol.TotalVolume) TotalLineVolume
		  FROM OrderLine ol
		  JOIN Product p
		    ON ol.ProductID = p.ProductID
		  JOIN ProductGroup pg
		    ON p.ProductGroupID = pg.ProductGroupID
		 WHERE ol.OrderID = oh.OrderID and 
			   pg.Name IN ('mouldings', 'loose stock', 'Spindles', 'Newels', 'Handrail Kits', 'DeckAncillaries', 
                      'DeckPosts', 'MDF Loose', 'Length stocks', 'MDF architraves loose', 'MDF skirtings loose', 'MDF windowboard loose')
	   ) tv

 WHERE oh.OrderNumber = 3296244

 

[Olaf Doschke]

>Column 'OrderHeader.OrderNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Well, in a query without any GROUP BY you can only query SUM(OrderLine.TotalVolume) and nothing else.

I would fix the field list like this:

SELECT 
		      MIN(OrderHeader.OrderNumber) as OrderNumber, 
                      MIN(OrderHeader.DateRequired) as DateRequires, 
                      MIN(Customer.Name) as Name, 
                      MIN(OrderHeader.DeliveryAddress) as DeliveryAddress, 
                      LEFT(RIGHT(OrderHeader.DeliveryAddress, 8), 2) AS PC, 
                      MIN(OrderHeader.NoOfLines) as NoOfLines, 
                      MIN(OrderHeader.TotalVolume) as TotalVolume, 
                      SUM(OrderLine.TotalVolume) AS [Loose m3]
                      MIN(ProductGroup.Name) AS Expr1
FROM         Product INNER JOIN
                      OrderLine ON Product.ProductID = OrderLine.ProductID INNER JOIN
                      OrderHeader INNER JOIN
                      Customer ON OrderHeader.CustomerID = Customer.CustomerID ON OrderLine.OrderID = OrderHeader.OrderID INNER JOIN
                      ProductGroup ON Product.ProductGroupID = ProductGroup.ProductGroupID

Where      (OrderHeader.OrderNumber = 3289248) AND (ProductGroup.Name IN ('mouldings', 'loose stock', 'Spindles', 'Newels', 'Handrail Kits', 'DeckAncillaries', 
                      'DeckPosts', 'MDF Loose', 'Length stocks', 'MDF architraves loose', 'MDF skirtings loose', 'MDF windowboard loose'))_

In general, as you only want the SUM over all records, you can't also get any varying detail info, so take MIN of anything else or just query the SUM() and no other fields.

If you have a receipt of bread and milk and bananas and you want the total price, you can't have the single detail positions at all. The total is just the total. That also means the only thinbgs you should add as "decoration" or side info is things you are sure are also constant for the OrderNumber, like the customer name. SQL still doesn't allow that in the field list, but you can take MIN() or MAX() of non varying data. It's a two sided sword for varying data though, as it results in one of the varying values. All you can be 100% sure even without knowing the meaning of the daa is the SUM() is the sum of all data.

Bye, Olaf.

 

[Cpreston]

Hi

Brilliant both work perfectly, thanks for all the help and advice.

Thanks