CODEWITHSUNDEEP
mysql
Phorce
Phorce

Reputation: 4642

MYSQL - Group By / Order By not working

I have the following data inside a table:

id   person_id   item_id  price 
1    1           1        10
2    1           1        20 
3    1           3        50

Now what I want to do is group by the item ID, select the id that has the highest value and take the price.

E.g. the sum would be: (20 + 50) and ignore the 10.

I am using the following:

SELECT SUM(`price`)
FROM
  (SELECT id, person_id, item_id, price
   FROM `table` tbl 
   INNER JOIN person p USING (person_id)
   WHERE p.person_id = 1
   ORDER BY id DESC) x 
GROUP BY item_id

However, this query is still adding (10 + 20 + 50), which is obviously not what I need to have.

Any ideas to where I am going wrong?

Upvotes: 0

Views: 2184

Answers (5)

Vipin Jain
Vipin Jain

Reputation: 3756

You have used group by in main query, but it is on subquery like 

SELECT  id, person_id, item_id, SUM(`price`) FROM   (  SELECT MAX(price)   FROM `table` tbl  WHERE p.person_id = 1 GROUP BY item_id  ) AS x

Upvotes: 0

Paul Spiegel
Paul Spiegel

Reputation: 31772

First of all - you don't need the person table, because the other table already contains the person_id. So i removed it from the examples.

Your query returns a sum of prices for each item. If you replace SELECT SUM(price) with SELECT item_id, SUM(price) you wil get

item_id   SUM(`price`)
1         30
3         50

But that is not what you want. Neither is it what you wrote in the question " (10 + 20 + 50)".

Now replacing the first line with SELECT id, item_id, SUM(price) you will get one row for each item with the highest id.

id   item_id   price
2    1         20
3    3         50

This works because of the "undocumented feature" of MySQL, wich allows you to select columns that are not listed in the GROUP BY clause and get the first row from the subselect each group (each item in this case).

Now you only need to sum the price column in an additional outer select

SELECT SUM(price)
FROM (
    SELECT id, item_id ,price
    FROM (
        SELECT id, person_id, item_id, price
        FROM `table` tbl 
        WHERE tbl.person_id = 1
        ORDER BY id DESC ) x 
    GROUP BY item_id 
) y

However i do not recomend to use that "feature". While it still works on MySQL 5.6, you never know if that will work with newer versions. It already doesn't work on MariaDB.

Instead you can determite the MAX(id) for each item in an subselect, select only the rows with the determined ids and get the summed price of them.

SELECT SUM(`price`)
FROM `table` tbl
WHERE tbl.id IN (
    SELECT MAX(tbl2.id)
    FROM `table` tbl2
    WHERE tbl2.person_id = 1
    GROUP BY tbl2.item_id
)

Another solution (wich internaly does the same) is

SELECT SUM(`price`)
FROM `table` tbl
JOIN (
    SELECT MAX(tbl2.id) as id
    FROM `table` tbl2
    WHERE tbl2.person_id = 1
    GROUP BY tbl2.item_id
) x ON x.id = tbl.id

Alex's solution also works fine, if the groups (number of rows per person and item) are rather small.

Upvotes: 0

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/40803/5

SELECT SUM(t1.price) 
FROM tbl t1
LEFT JOIN tbl t2
ON t1.person_id= t2.person_id
  AND t1.item_id = t2.item_id
  AND t1.id<t2.id
WHERE t1.person_id = 1
  AND t2.id IS NULL;

Upvotes: 1

martin.malek
martin.malek

Reputation: 2218

I'm not sure if this is the only requirement you have. If so, try this.

SELECT SUM(price) FROM (SELECT MAX(price) FROM table WHERE person_id = 1 GROUP BY item_id)

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Here is what you are trying to achieve. First you need grouping in a subquery and not in outer query. In outer query you need only sum:

SELECT SUM(`price`)
FROM
  (SELECT MAX(price) as price
   FROM `table` tbl 
   INNER JOIN person p USING (person_id)
   WHERE p.person_id = 1
   GROUP BY item_id) x

Upvotes: 2

Related Questions