INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

Sum of all group

Sum of all group

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





CODE --> SQL

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

RE: Sum of all group

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

RE: Sum of all group

(OP)
Hi

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

Code is like below now

CODE --> SQL

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

RE: Sum of all group

(OP)
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

RE: Sum of all group

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.

RE: Sum of all group

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:

CODE

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 

RE: Sum of all group

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

CODE --> SQL

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.

CODE --> sql

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 

RE: Sum of all group

>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:

CODE

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.

RE: Sum of all group

(OP)
Hi

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

Thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close