merging results from two tables
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;
