MySQL Average Function not returning expected result

Question

I need to get average of the total amount for only those orders starting from 1st January 2015. Somehow, I am not getting the expected the result.

Problem Statement: Return for each customer their id, name and average total order amount for orders starting on January 1, 2015 (inclusive). Only show customers that have placed at least 2 orders.

Since there are multiple conditions involved, I have created the query including where, group by and having clause as shown in the query below

SELECT c.CustomerId, c.CustomerName, AVG(o.total)
FROM db_order.Customer c JOIN db_order.orders o 
on c.customerId = o.customerId
where o.orderDate >= 01-01-2015 
group by o.CustomerId, c.CustomerName
having count(o.orderId) >= 2

The expected output has 6 rows and actual has 8. And there is a difference in returned results even in the average total.

SQL Fiddle: http://sqlfiddle.com/#!9/d872bf Db Fiddle with complete data: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=dd6fcb2a41634b0e4beb8acc3288e922

could you add some example data see meta.stackoverflow.com/questions/333952/… — nbk
– nbk, Commented Nov 3, 2019 at 19:06

VBoka · Accepted Answer · 2019-11-03 19:16:13Z

1

Does this resolve your problem:

SELECT c.CustomerId
       , c.CustomerName
       , AVG(o.total)
FROM Customer c 
JOIN Orders o on c.customerId = o.customerId
where o.orderDate >= '2015-01-01' 
group by o.CustomerId
      , c.CustomerName
having count(o.orderId) >= 2

Here is the DEMO Showing 6 rows as asked...

answered Nov 3, 2019 at 19:16

VBoka

9,0933 gold badges19 silver badges27 bronze badges

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

Collectives™ on Stack Overflow

MySQL Average Function not returning expected result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related