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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need Help with SUM( )

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:
-----------------------------------------------
SELECT
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,
SUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"

FROM
dbo.sitestorepro_orders
INNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderID
INNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdID
WHERE
dbo.sitestorepro_orders.OrderID = 15233 AND
dbo.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]
SELECT
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,
SUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"

FROM
dbo.sitestorepro_orders
INNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderID
INNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdID
WHERE
dbo.sitestorepro_orders.OrderID = 15233 AND
dbo.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]
SELECT
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,
SUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"

FROM
dbo.sitestorepro_orders
INNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderID
INNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdID
WHERE
dbo.sitestorepro_orders.OrderID = 15233 AND
dbo.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.
Go to Top of Page

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 ValidAMT
rec-1 1.5 1.5
rec-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]
SELECT
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,
SUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"

FROM
dbo.sitestorepro_orders
INNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderID
INNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdID
WHERE
dbo.sitestorepro_orders.OrderID = 15233 AND
dbo.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]
SELECT
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,
SUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"

FROM
dbo.sitestorepro_orders
INNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderID
INNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdID
WHERE
dbo.sitestorepro_orders.OrderID = 15233 AND
dbo.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.

Go to Top of Page

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]
SELECT
SUM(dbo.sitestorepro_orderdetails.LineTotal) as "ValidAMT"

FROM
dbo.sitestorepro_orders
INNER JOIN dbo.sitestorepro_orderdetails ON dbo.sitestorepro_orders.OrderID = dbo.sitestorepro_orderdetails.OrderID
INNER JOIN dbo.sitestorepro_products ON dbo.sitestorepro_orderdetails.ProdID = dbo.sitestorepro_products.ProdID
WHERE
dbo.sitestorepro_orders.OrderID = 15233 AND
dbo.sitestorepro_products.MasterCatID NOT IN (3,4,8,11)[/CODE]
Go to Top of Page
   

- Advertisement -