MySQL: SUM() feature not working

Question

I am trying to sum a column I created by multiplying two other columns and it doesn't seem to be working. I get an error every time I attempt to put in SUM(PriceTimesQuantity)

Any and all help would be great!

Here is my query:

select 
  OrderedProduct.orderId, 
  CustOrder.customerId, 
  CustOrder.orderDate, 
  OrderedProduct.paidPrice * OrderedProduct.qtyOrdered as PriceTimesQuantity
from OrderedProduct
join CustOrder
  on CustOrder.orderId=OrderedProduct.orderId
where orderDate between '2014-01-01' and '2014-12-31'
group by orderDate

What's the error that you get? What's the full query that you are trying to execute when you are using SUM(PriceTimesQuantity)? — Taryn
– Taryn, Commented Nov 8, 2015 at 19:06
Also note that SUM() is defined by the GROUP BY statement, therefore if you are grouping by orderDate, some values in the query like CustOrder.customerId won't make much sense. — jso
– jso, Commented Nov 8, 2015 at 19:11

Gordon Linoff · Accepted Answer · 2015-11-09 00:06:22Z

1

You should only include columns in the select that are either in the group by or that are in aggregation functions. Hence, no columns such as orderId.

This is probably closer to what you are trying to do:

select co.orderDate, 
       sum(op.paidPrice * op.qtyOrdered) as PriceTimesQuantity
from OrderedProduct op join
     CustOrder co
     on co.orderId = op.orderId
where co.orderDate between '2014-01-01' and '2014-12-31'
group by co.orderDate;

Your specific error is that you cannot reference an alias defined in the same select clause.

edited Nov 9, 2015 at 0:06

answered Nov 8, 2015 at 19:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zach Kaplan Over a year ago

Sorry for the late response. My professor wants to see those columns when I submit my work and that's why I have to include the nonsense ones such as orderId in the select statement. Is there a work around? I also tried the query you listed above and ran into a bunch of errors... :(

Collectives™ on Stack Overflow

MySQL: SUM() feature not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related