GROUP BY lname ORDER BY showing wrong results

Question

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 issue could be?

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

table comp_user:

user_id int(11) NO  PRI NULL    auto_increment
fname   varchar(255)    NO      NULL    
lname   varchar(255)    NO      NULL    
storename   varchar(255)    NO      NULL    
city    varchar(255)    NO      NULL    
phone   varchar(255)    NO      NULL    
wp_id   int(11) NO      NULL    
type    varchar(255)    NO      NULL

table comp_sale

prod_id int(11) NO      NULL    
sale_id int(11) NO      NULL    
serial  varchar(255)    NO      NULL

table comp_product

product_id  int(11) NO  PRI NULL    auto_increment
description varchar(255)    NO      NULL    
type    varchar(255)    NO      NULL    
cylinda_num int(11) NO      NULL    
eel_num int(11) NO      NULL    
point   int(11) NO      NULL

Why are you grouping by lName? I believe that is the reason for your incorrect results — Rahul, Jun 05 '12 at 09:34
isn't query should be like `GROUP BY u.lname ORDER BY p.points` — Darshana, Jun 05 '12 at 09:37

score 10 · Accepted Answer · answered Jun 05 '12 at 09:33

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

score 0 · Answer 2 · edited Jun 05 '12 at 18:07

0

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;

edited Jun 05 '12 at 18:07

manurajhada

5,284
3
24
43

answered Jun 05 '12 at 10:04

papacostas

848
1
9
24

GROUP BY lname ORDER BY showing wrong results

2 Answers2

Linked