GROUP BY Lname ORDER BY Showing Wrong Results

This query I've written, listing the top 25 in a sales competition, is showing the wrong results. Users aren't showing that should be pretty far up on the list. Any idea what the i

Solution 1:

Try using a proper, ANSI standard GROUP BY

SELECT
    u.fname, u.lname, SUM(p.point) as points
FROM 
    comp_sale s
    JOIN
    comp_product p ON s.prod_id = p.product_id 
    JOIN
    comp_user u ON s.sale_id = u.wp_id
GROUP BY
    u.fname, u.lname
ORDER BY 
    points DESC 
LIMIT 25

Also, use explicit JOINs for clarity

Solution 2:

Answer my own question: I wasn't grouping by fname and lname but only lname.

SELECT fname, lname, SUM(point) as points FROM cylinda_sale s, cylinda_product p, cylinda_user u  WHERE s.prod_id = p.product_id AND s.sale_id = u.wp_id GROUP BY lname, fname ORDER BY points DESC limit 25;