phpmyadmin Question posted in
The official documentation can be found here.

How to get best items summing duplicates in SQL? – Phpmyadmin

I’m trying to make a list of the best items in the database. An item is better if the profit is higher. The problem is an item a can have duplicates like 500 profit at 10 o’clock and 750 profit at 14 o’clock.

My current code would show both attempts and php I filter this. This way I get the item A once, but I can’t tell the total profit of item A.

My SQL statement is this: SELECT item FROM category WHERE endtime >=? ORDER BY profit DESC LIMIT 15
endtime is a unixtime stamp so it shows items from the last week.

And with php, I filter using array_unique($items, SORT_REGULAR);

I would like SQL to give a list of 5 most profitable items with profit and item where duplicates get to add up.

So this: a 500, b 250, c 350, b 100, a 25, c 50
becomes: a 525, c 400, b 350

Tags:

2

Answers


  1. 0

    If i understood correctly you have a table given like this: 

    item | profit
----------- 
  a  | 500
  b  | 250
  c  | 350 
  b  | 100
  a  | 25
  c  | 50

    and you want: 

    item | profit
----------- 
  a  | 525
  b  | 350
  c  | 400

    This can be achieved quite easily using GROUP BY and SUM

    SELECT item, sum(profit)
FROM category
WHERE endtime >=?
GROUP BY id;
    Login or Signup to reply.
  2. 0

    This query should give you the result you want (without sample data it’s hard to be certain). Basically you need to use SUM to sum the profit and GROUP BY item to get sums for each different item.

    SELECT item, SUM(profit) AS total_profit 
FROM category
WHERE endtime >=?
GROUP BY item
ORDER BY total_profit DESC
LIMIT 5
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search