MySQL SELECT query returns wrong result

Question

I have a table orders(c_id(INT),o_date(DATE),o_price(INT)). I have to find out the id and MAX value of total price for each id in the current year. The table is as follows:

c_id    o_date     o_price
   1    2017-08-27      30    
   2    2017-05-25     100    
   2    2017-05-02      20    
   1    2017-02-23      80    
   3    2017-01-26      60   
   4    2016-04-22      10    
   2    2016-03-15       5   
   1    2015-09-01       1

I code as follow:

SELECT c_id, MAX(m_price)  
FROM (  SELECT c_id, SUM(o_price) AS
 m_price FROM orders WHERE  
(       YEAR(o_date) = YEAR(curdate())  ) 
GROUP
> BY c_id )AS T

the MAX value return right but the id is not fit with that MAX value. Has anyone a suggestion for my situation? Thank you in advance!!

Tim Biegeleisen · Accepted Answer · 2017-06-24 10:22:06Z

1

If there is guaranteed to be only one c_id which has the largest sum, or you don't mind returning only one result in the event of a tie, then you can just LIMIT 1 along with an ORDER BY to get the max result.

SELECT
    c_id,
    SUM(o_price) AS m_price
FROM orders
WHERE
    YEAR(o_date) = YEAR(CURDATE())
GROUP BY c_id
ORDER BY m_price DESC
LIMIT 1

If there could be a tie for maximum sum, then there are workarounds which are slightly uglier, e.g.

SELECT
    c_id,
    SUM(o_price) AS m_price
FROM orders
WHERE
    YEAR(o_date) = YEAR(CURDATE()) AND
GROUP BY c_id
HAVING SUM(o_price) =
(
    SELECT MAX(t.m_price)
    FROM
    (SELECT SUM(o_price) AS m_price FROM orders
     WHERE YEAR(o_date) = YEAR(CURDATE()) GROUP BY c_id) t
)

The above query is a bit verbose, and in other databases we would use analytic functions to handle this. But since MySQL does not support these out of the box we need to use another way.

edited Jun 24, 2017 at 10:22

answered Jun 24, 2017 at 9:59

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

2 Comments

vodangpro123 Over a year ago

However, i think it is not an optimized code, because we will have a case that there're two MAX value, so it will be not true if we use this code.

Tim Biegeleisen Over a year ago

@vodangpro123 I gave you an alternative query which will retain all duplicates.

Gordon Linoff · Accepted Answer · 2017-06-24 11:40:10Z

1

If you have multiple prices, the following is slightly simpler than Tim's code:

SELECT o.c_id, SUM(o.o_price) AS m_price
FROM orders o
WHERE YEAR(o.o_date) = YEAR(CURDATE())
GROUP BY o.c_id
HAVING m_price = (SELECT SUM(o2.o_price)
                  FROM orders o2
                  WHERE YEAR(o2.o_date) = YEAR(CURDATE()
                  GROUP BY c_id
                  ORDER BY SUM(o2.o_price) DESC
                  LIMIT 1
                 );

In practice, using variables might be faster.

SELECT o.*
FROM (SELECT o.*, @maxp := GREATEST(@maxp, m_price)
      FROM (SELECT o.c_id, SUM(o.o_price) AS m_price
            FROM orders o
            WHERE YEAR(o.o_date) = YEAR(CURDATE())
            GROUP BY o.c_id
           ) o CROSS JOIN
           (SELECT @maxp := 0) params
     ) o
WHERE m_price = @maxp;

answered Jun 24, 2017 at 11:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Tim Biegeleisen Over a year ago

Both answers nicer than my own, and on any other database with analytic functions we'd probably be using the analog of your second query +1.

Muhammad Muazzam · Accepted Answer · 2017-06-24 10:03:28Z

0

Adjust your query as

 SELECT c_id, MAX(m_price)  
    FROM (  SELECT c_id, SUM(o_price) AS
     m_price FROM orders WHERE  
     YEAR(o_date) = YEAR(curdate())
    ) 
    GROUP BY c_id

answered Jun 24, 2017 at 10:03

Muhammad Muazzam

2,8006 gold badges37 silver badges64 bronze badges

Comments

vodangpro123 · Accepted Answer · 2017-06-29 16:45:29Z

0

My solution for this is: set @p1 = (SELECT MAX(m_price) FROM ( SELECT c_id, SUM(o_price) AS m_price FROM orders WHERE ( YEAR(o_date) = YEAR(curdate()) ) GROUP BY c_id ) as T );

SELECT c_id from ( SELECT c_id, sum(o_price) as m_price from orders WHERE ( YEAR(o_date) = YEAR(curdate()) )group by c_id ) as a WHERE m_price = @p1

edited Jun 29, 2017 at 16:45

answered Jun 24, 2017 at 12:12

vodangpro123

236 bronze badges

Collectives™ on Stack Overflow

MySQL SELECT query returns wrong result

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related