Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
tobtob
Starting Member
7 Posts |
Posted - 2011-08-16 : 18:22:54
|
I need to get the SUM() of the field called LineTotal. I keep getting an error telling me: [Err] 42000 - [SQL Server]Column 'dbo.sitestorepro_orders.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.I've tried everything I can think of and/or google, including sub-selects which really got me lost. Can anyone suggest how to re-write this select statement to get the SUM of the LineTotal's ?here it is:-----------------------------------------------SELECTdbo.sitestorepro_orders.OrderID,dbo.sitestorepro_orderdetails.ProdID,dbo.sitestorepro_orderdetails.DetailPrice,dbo.sitestorepro_orderdetails.DetailAdjustedPrice,dbo.sitestorepro_orderdetails.DetailQuantity,dbo.sitestorepro_products.MasterCatID,dbo.sitestorepro_products.SubCatID,dbo.sitestorepro_orderdetails.LineTotal,SUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"FROMdbo.sitestorepro_ordersINNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderIDINNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdIDWHEREdbo.sitestorepro_orders.OrderID = 15233 ANDdbo.sitestorepro_products.MasterCatID NOT IN (3,4,8,11)----------------------------------------------------------Thanks for any help you can provide. |
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-08-16 : 18:44:17
|
With any aggregate function (ie SUM, COUNT, AVG etc) any column in the select list that is not being aggregated must be in a Group By Clause. So each of the other columns you hae in your select statement need to be in the Group By clause except for the Summed column:[CODE]SELECTdbo.sitestorepro_orders.OrderID,dbo.sitestorepro_orderdetails.ProdID,dbo.sitestorepro_orderdetails.DetailPrice,dbo.sitestorepro_orderdetails.DetailAdjustedPrice,dbo.sitestorepro_orderdetails.DetailQuantity,dbo.sitestorepro_products.MasterCatID,dbo.sitestorepro_products.SubCatID,dbo.sitestorepro_orderdetails.LineTotal,SUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"FROMdbo.sitestorepro_ordersINNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderIDINNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdIDWHEREdbo.sitestorepro_orders.OrderID = 15233 ANDdbo.sitestorepro_products.MasterCatID NOT IN (3,4,8,11)Group by dbo.sitestorepro_orders.OrderID,dbo.sitestorepro_orderdetails.ProdID,dbo.sitestorepro_orderdetails.DetailPrice,dbo.sitestorepro_orderdetails.DetailAdjustedPrice,dbo.sitestorepro_orderdetails.DetailQuantity,dbo.sitestorepro_products.MasterCatID,dbo.sitestorepro_products.SubCatID,dbo.sitestorepro_orderdetails.LineTotal[/CODE]The only issue is that you already have "LINETOTAL" in the select list unsummed which means you'll be grouping by it as well. Probably aren't going to yield a column sum for that field. Try taking it out of the select list and the group by like this:[CODE]SELECTdbo.sitestorepro_orders.OrderID,dbo.sitestorepro_orderdetails.ProdID,dbo.sitestorepro_orderdetails.DetailPrice,dbo.sitestorepro_orderdetails.DetailAdjustedPrice,dbo.sitestorepro_orderdetails.DetailQuantity,dbo.sitestorepro_products.MasterCatID,dbo.sitestorepro_products.SubCatID,SUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"FROMdbo.sitestorepro_ordersINNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderIDINNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdIDWHEREdbo.sitestorepro_orders.OrderID = 15233 ANDdbo.sitestorepro_products.MasterCatID NOT IN (3,4,8,11)Group by dbo.sitestorepro_orders.OrderID,dbo.sitestorepro_orderdetails.ProdID,dbo.sitestorepro_orderdetails.DetailPrice,dbo.sitestorepro_orderdetails.DetailAdjustedPrice,dbo.sitestorepro_orderdetails.DetailQuantity,dbo.sitestorepro_products.MasterCatID,dbo.sitestorepro_products.SubCatID[/CODE]And you'll probably want to think about taking some of the other columns out of the select and group by clauses as well. Without knowing what your details are, it's hard to say what your summaries should look like. |
|
|
tobtob
Starting Member
7 Posts |
Posted - 2011-08-16 : 22:38:28
|
Thanks for a great, very detailed answer. but ... I probably wrote the initial select wrong. The result returned gives me the same value for LineTotal and ValidAMT. 2 records are returned: LineTotal ValidAMTrec-1 1.5 1.5rec-2 0.5 0.5 What I need to get is one total called ValidAMT with a value of 2 in this case.Can you suggest a way to do this other than looping through the records with ASP code. I'd like to do it all with the select if possible.Thanks.quote: Originally posted by flamblaster With any aggregate function (ie SUM, COUNT, AVG etc) any column in the select list that is not being aggregated must be in a Group By Clause. So each of the other columns you hae in your select statement need to be in the Group By clause except for the Summed column:[CODE]SELECTdbo.sitestorepro_orders.OrderID,dbo.sitestorepro_orderdetails.ProdID,dbo.sitestorepro_orderdetails.DetailPrice,dbo.sitestorepro_orderdetails.DetailAdjustedPrice,dbo.sitestorepro_orderdetails.DetailQuantity,dbo.sitestorepro_products.MasterCatID,dbo.sitestorepro_products.SubCatID,dbo.sitestorepro_orderdetails.LineTotal,SUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"FROMdbo.sitestorepro_ordersINNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderIDINNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdIDWHEREdbo.sitestorepro_orders.OrderID = 15233 ANDdbo.sitestorepro_products.MasterCatID NOT IN (3,4,8,11)Group by dbo.sitestorepro_orders.OrderID,dbo.sitestorepro_orderdetails.ProdID,dbo.sitestorepro_orderdetails.DetailPrice,dbo.sitestorepro_orderdetails.DetailAdjustedPrice,dbo.sitestorepro_orderdetails.DetailQuantity,dbo.sitestorepro_products.MasterCatID,dbo.sitestorepro_products.SubCatID,dbo.sitestorepro_orderdetails.LineTotal[/CODE]The only issue is that you already have "LINETOTAL" in the select list unsummed which means you'll be grouping by it as well. Probably aren't going to yield a column sum for that field. Try taking it out of the select list and the group by like this:[CODE]SELECTdbo.sitestorepro_orders.OrderID,dbo.sitestorepro_orderdetails.ProdID,dbo.sitestorepro_orderdetails.DetailPrice,dbo.sitestorepro_orderdetails.DetailAdjustedPrice,dbo.sitestorepro_orderdetails.DetailQuantity,dbo.sitestorepro_products.MasterCatID,dbo.sitestorepro_products.SubCatID,SUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"FROMdbo.sitestorepro_ordersINNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderIDINNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdIDWHEREdbo.sitestorepro_orders.OrderID = 15233 ANDdbo.sitestorepro_products.MasterCatID NOT IN (3,4,8,11)Group by dbo.sitestorepro_orders.OrderID,dbo.sitestorepro_orderdetails.ProdID,dbo.sitestorepro_orderdetails.DetailPrice,dbo.sitestorepro_orderdetails.DetailAdjustedPrice,dbo.sitestorepro_orderdetails.DetailQuantity,dbo.sitestorepro_products.MasterCatID,dbo.sitestorepro_products.SubCatID[/CODE]And you'll probably want to think about taking some of the other columns out of the select and group by clauses as well. Without knowing what your details are, it's hard to say what your summaries should look like.
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-08-17 : 00:04:43
|
No need to loop that I can see. It should be a simple sum function. Taking all the other columns out of the query should suffice. Try:[CODE]SELECTSUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"FROMdbo.sitestorepro_ordersINNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderIDINNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdIDWHEREdbo.sitestorepro_orders.OrderID = 15233 ANDdbo.sitestorepro_products.MasterCatID NOT IN (3,4,8,11)[/CODE] |
|
|
|
|
|
|
|