SQL: Get Total Prices of Child Records

[wessleym]

This is a continuation of a question I asked last year: https://www.neowin.net/forum/topic/947194-sql-get-total-prices-of-child-records/

Let's say I have a few tables constructed like this:

Products:
ProductID  Price  Quantity
1          10     2
2          15     3

Product Options:
ProductOptionID  ProductID  Price  Quantity
1                1          2      2
2                1          4      1
3                2          6      1

Now, what if I wanted the total price of Product ID 1? Of course, that's 2 * (10 + ((2 * 2) + (4 * 1))) = 36. But how could I achieve that in SQL? I've written several queries, but I always get weird errors. The below is what bane7378 and mastermate came up with last time:

select products.prodid,
     isnull(sum(options.price*options.qty),0)+products.prodprice 
from products left outer join options 
on products.prodid= options.prodid 
group by products.prodid,products.prodprice

At the time, I wasn't using a Quantity field for each Product. (Only the Product Option's had Quantity fields.)

So how can I modify this query? Thank you!

[kjordan2001]

This is a continuation of a question I asked last year: https://www.neowin.net/forum/topic/947194-sql-get-total-prices-of-child-records/

Let's say I have a few tables constructed like this:

Products:
ProductID  Price  Quantity
1          10     2
2          15     3

Product Options:
ProductOptionID  ProductID  Price  Quantity
1                1          2      2
2                1          4      1
3                2          6      1

Now, what if I wanted the total price of Product ID 1? Of course, that's 2 * (10 + ((2 * 2) + (4 * 1))) = 36. But how could I achieve that in SQL? I've written several queries, but I always get weird errors. The below is what bane7378 and mastermate came up with last time:

select products.prodid,
     isnull(sum(options.price*options.qty),0)+products.prodprice 
from products left outer join options 
on products.prodid= options.prodid 
group by products.prodid,products.prodprice

At the time, I wasn't using a Quantity field for each Product. (Only the Product Option's had Quantity fields.)

So how can I modify this query? Thank you!

It should just need:

select products.prodid,
     products.qty*(isnull(sum(options.price*options.qty),0)+products.prodprice)
from products left outer join options 
on products.prodid= options.prodid 
group by products.prodid

Also not sure why you were grouping by the prodprice before as well (unless you theoretically have more than one price per product).

[wessleym]

I'm getting these errors:

Msg 8120, Level 16, State 1, Line 2
Column 'products.qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'products.prodprice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Should I group by qty and prodprice?

[bane7378]

I'm getting these errors:

Msg 8120, Level 16, State 1, Line 2
Column 'products.qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'products.prodprice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Should I group by qty and prodprice?

It seems to work fine with kjordan2001's adjustment but I'm using mysql to test which is more forgiving than SQL Server. You might try moving the multiplication of the quantity from products to the end of the statement. I think grouping by quantity would just be unnecessary however. My guess without testing it more is that SQL doesn't like the fact that your aggregate is starting with another field.

I'll try to get this from home with SQL Server later if I can find some time but hopefully someone else will have a more definitive answer for you before I get to that.

Good Luck,

Lou

[kjordan2001]

I'm getting these errors:

Msg 8120, Level 16, State 1, Line 2
Column 'products.qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'products.prodprice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Should I group by qty and prodprice?

Looks like SQL Server does require those to either be in the GROUP BY or have a function like avg or max or sum on them, so I'd just put them in the group by since you don't need to do any aggregation on them.

[wessleym]

I figured out the "group by" problem. Please see this article:

http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

I perform the sum in one query and then inner join it with the items which don't need to be summed. Thank you, everyone!