merging results from two tables

[javaQQ]

I would like to ask for some help fixing this query:
SELECT u.user_id, u.first_name, u.last_name, SUM( p.job_price ) AS 'used'
FROM TestUsers AS u, TestPriceline AS p GROUP BY user_id;

I want to merge columns from two tables to come up with the following result:
+---------+------------+-----------+------+
| user_id | first_name | last_name | used |
+---------+------------+-----------+------+
| abc-A | Albert | Allen | 1.00 |
| abc-B | Bill | Bell | 2.00 |
| abc-C | Carla | Carey | 2.00 |
| abc-D | Dana | Dawkins | 2.00 |
+---------+------------+-----------+------+
4 rows in set (0.01 sec)

My query gives me this reult:
mysql> SELECT u.user_id, u.first_name, u.last_name, SUM( p.job_price ) AS 'used'
-> FROM TestUsers AS u, TestPriceline AS p GROUP BY user_id;
+---------+------------+-----------+------+
| user_id | first_name | last_name | used |
+---------+------------+-----------+------+
| abc-A | Albert | Allen | 7.00 |
| abc-B | Bill | Bell | 7.00 |
| abc-C | Carla | Carey | 7.00 |
| abc-D | Dana | Dawkins | 7.00 |
+---------+------------+-----------+------+
4 rows in set (0.01 sec)
Cose, but it gives me the sum of the entire "job_price" column as the sum for each user


I start with two tables:
mysql> SELECT * FROM TestUsers;
+------------+---------+------------+-----------+
| account_id | user_id | first_name | last_name |
+------------+---------+------------+-----------+
| abc | abc-A | Albert | Allen |
| abc | abc-B | Bill | Bell |
| abc | abc-C | Carla | Carey |
| abc | abc-D | Dana | Dawkins |
+------------+---------+------------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM TestPriceline;
+------------+---------+--------+-----------+
| account_id | user_id | job_id | job_price |
+------------+---------+--------+-----------+
| abc | abc-A | abc#1 | 1.00 |
| abc | abc-B | abc#2 | 1.00 |
| abc | abc-B | abc#3 | 1.00 |
| abc | abc-C | abc#4 | 1.00 |
| abc | abc-C | abc#5 | 1.00 |
| abc | abc-D | abc#6 | 1.00 |
| abc | abc-D | abc#7 | 1.00 |
+------------+---------+--------+-----------+
7 rows in set (0.00 sec)

The following queries work properly:

SELECT user_id, first_name, last_name FROM TestUsers GROUP BY user_id;
+---------+------------+-----------+
| user_id | first_name | last_name |
+---------+------------+-----------+
| abc-A | Albert | Allen |
| abc-B | Bill | Bell |
| abc-C | Carla | Carey |
| abc-D | Dana | Dawkins |
+---------+------------+-----------+
4 rows in set (0.02 sec)

SELECT user_id, SUM( job_price ) AS 'used' FROM TestPriceline GROUP BY user_id;
+---------+------+
| user_id | used |
+---------+------+
| abc-A | 1.00 |
| abc-B | 2.00 |
| abc-C | 2.00 |
| abc-D | 2.00 |
+---------+------+
4 rows in set (0.03 sec)


The entire code is below:
/* ---------------------------------------------------- Code Begins Here: -----------------
DROP TABLE IF EXISTS TestUsers;
CREATE TABLE TestUsers (

account_id varchar(15),
user_id varchar(15),
first_name varchar(15),
last_name varchar(15)

)
;

INSERT INTO TestUsers ( account_id, user_id, first_name, last_name )
VALUES ( "abc", "abc-A", "Albert", "Allen" )
;

INSERT INTO TestUsers ( account_id, user_id, first_name, last_name )
VALUES ( "abc", "abc-B", "Bill", "Bell" )
;

INSERT INTO TestUsers ( account_id, user_id, first_name, last_name )
VALUES ( "abc", "abc-C", "Carla", "Carey" )
;

INSERT INTO TestUsers ( account_id, user_id, first_name, last_name )
VALUES ( "abc", "abc-D", "Dana", "Dawkins" )
;

DROP TABLE IF EXISTS TestPriceline;
CREATE TABLE TestPriceline (

account_id varchar(15),
user_id varchar(15),
job_id varchar(15),
job_price decimal(10,2)

)
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-A", "abc#1", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-B", "abc#2", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-B", "abc#3", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-C", "abc#4", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-C", "abc#5", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-D", "abc#6", 1.00 )
;

INSERT INTO TestPriceline ( account_id, user_id, job_id, job_price )
VALUES ( "abc", "abc-D", "abc#7", 1.00 )
;

SELECT user_id, SUM( job_price ) AS 'used' FROM TestPriceline GROUP BY user_id;

SELECT user_id, first_name, last_name FROM TestUsers GROUP BY user_id;

SELECT u.user_id, u.first_name, u.last_name, SUM( p.job_price ) AS 'used'
FROM TestUsers AS u, TestPriceline AS p GROUP BY user_id;

 

[hvass]

select
u.user_id,
u.first_name,
u.last_name,
SUM( p.job_price ) AS 'used'
from TestUsers u inner join TestPriceline p using(account_id,user_id)
group by user_id;

 

[guelphdad]

hvass, your group by clause is incorrect. You need to include all non-aggregate fields from your select clause in your group by clause. While MySQL allows you to get away with this they do have a note on grouping on hidden fields. They mention this because unless all values in the fields not listed is unique you can end up with incorrect results.

 

[hvass]

Thank you I stand corrected

select
u.user_id,
u.first_name,
u.last_name,
SUM( p.job_price ) AS 'used'
from TestUsers u inner join TestPriceline p using(account_id,user_id)
group by user_id,first_name,last_name;

I had made the implict assumption that user_id was unique.

 

[guelphdad]

The first and last names would also have to be unique.

By the way MySQL is the only db that does not throw an error on this, all the other ones would give an error message.

I'm sure there are times that it is beneficial to be able to group with the hidden fields, but more often than not it isn't the right thing to do.

I don't think many people are aware of that part of the manual since no error or warning is thrown to get you to look into it further.

 

[TonyGroves]

I like the way MySQL allows you to omit unnecessary grouping fields. If there is exactly one first_name and one last_name for each user_id, then there's no logical reason why you should be forced to also group by first_name and last_name. And when you add other fields like phone_number, email_address, and maybe many more, it gets even more ludicrous.

Standard SQL may insist on it, but I'm afraid in this case, the standard seems silly, and I'm quite happy to take advantage of MySQL's sensible shortcut. The only reason I might stick with the standard is if portability is a concern.

 

[guelphdad]

id first last
1 Joe Brown
2 Mary Brown
3 Joe Soap

The only column here that contains unique data is the id column. The first and last names include duplicate data even though they don't have identical first and last names for any of the rows. Since each of the columns being requested DOES NOT have unique data you run the risk of unpredictable results just like the mysql manual warns you about.

 

[TonyGroves]

Those name fields may not be unique in the result set, but they are unique within their respective groups - there is exactly one distinct first_name and one distinct last_name for each user_id. Therefore, as the manual says, at

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

, they are redundant, and do not need to be included in the grouping list. This approach eliminates unnecessary sorting which can lead to better performance, and is of course simpler and clearer.