How Do I Remove Duplicate and Get the Max Value After a SUM() Group By? postgresql-CodePudding

I have the following data:

machine_name	reward_points	recipt_no
LEKKSERVER01	57.5	43543
LEKKSERVER01	34	53453
HERMSERVER	23	35353
IKOYSERVER01	24	35636
IKOYSERVER01	20.5	63653

When I used this query

SELECT l.machine_name, 
       t.points
FROM
(
    SELECT machine_name, 
           SUM(reward_points) as points
    FROM loyalty
    GROUP BY machine_name
) t 
JOIN loyalty l ON l.machine_name = t.machine_name

I get the below result

machine_name	points
LEKKSERVER01	91.5
LEKKSERVER01	91.5
HERMSERVER	23
IKOYSERVER01	44.5
IKOYSERVER01	44.5

However, the result I am expecting is this.

machine_name	points
LEKKSERVER01	91.5
HERMSERVER	23
IKOYSERVER01	44.5

My question is, How can I remove the duplicates and possibly return the row with the highest points?

I would like to return something like this

machine_name	points
LEKKSERVER01	91.5

CodePudding user response：

Remove JOIN and use HAVING

SELECT machine_name, 
           SUM(reward_points) as points
FROM loyalty
GROUP BY machine_name
HAVING SUM(reward_points) >= all (
    SELECT SUM(reward_points)
    FROM loyalty
    GROUP BY machine_name
)

This solution can return more than one row if there is more than one machine_name with the highest points (i.e. it handles ties). The equivalent SQL in PostgreSql 13 should be

SELECT machine_name, 
           SUM(reward_points) as points
FROM loyalty
GROUP BY machine_name
ORDER BY points DESC
FETCH FIRST 1 ROWS WITH TIES

CodePudding user response：

Use max(t.points) along with group by machine_name:

SELECT l.machine_name,
       Max(t.points) AS points
FROM   (SELECT machine_name,
               Sum(reward_points) AS points
        FROM   loyalty
        GROUP  BY machine_name) t
       JOIN loyalty l
         ON l.machine_name = t.machine_name
GROUP  BY machine_name

To get only one row that has the highest points:

SELECT l.machine_name,
       Max(t.points) AS points
FROM   (SELECT machine_name,
               Sum(reward_points) AS points
        FROM   loyalty
        GROUP  BY machine_name) t
       JOIN loyalty l
         ON l.machine_name = t.machine_name
GROUP  BY machine_name
ORDER  BY points DESC
LIMIT  1