SQL: error implied with join, but I think it's in SELECT statement, thoughts?

Question

SELECT customer_id, payment, SUM(payment) FROM telemon_payments_data payments LEFT JOIN telemon_customer_data customer ON payments.customer_id = customer.customer_id GROUP BY 1 HAVING customer.account_created_on between 2016-01-01 and 2016-01-31

+++++++++++

evidently, my customer_id field is ambigious, because that's the error code. Thoughts? Thanks!

Thanks, I could've sworn I saw an underscore. I'll have to look at GROUP BY. — herbacidal, Mar 21 '17 at 16:33
Actually, I'm not sure if I should have edited or just started a new question. the error given now is not with the join but, it's the same basic code, so I wanted to append it to this question, and the site suggested editing. — herbacidal, Mar 22 '17 at 00:23
for recommended behavior going forward, please advise, thanks. — herbacidal, Mar 22 '17 at 00:23
MySQL problem here is that you have one `GROUP BY` but selecting `payment` too. Most SQLs will not allow that. MySQL is dumb and picks one for you - check this behaviour is what you want. — LoztInSpace, Mar 22 '17 at 00:25
I don't understand. I can't choose sum on a column that isn't in the GROUP BY? — herbacidal, Mar 22 '17 at 02:13

score 0 · Answer 1 · answered Mar 20 '17 at 03:34

0

as Mitch Wheat said, the problem is space in your 2nd table

use January Cohort AS January

or [January Cohort] AS January

answered Mar 20 '17 at 03:34

Boyke Ferdinandes

1

!#$E, you're right, I thought I had underscore there! – herbacidal Mar 20 '17 at 03:46
Followup: can you JOIN a table to a view? – herbacidal Mar 20 '17 at 03:47
yes you can. just assume that View is a Table. treat it the same – Boyke Ferdinandes Mar 20 '17 at 03:52

score 0 · Answer 2 · answered Mar 23 '17 at 12:19

0

The ambiguous customer_id error is because of the fact that both tables have a customer_id field. You need to tell the query which table to pull the column from (i.e., select customer.customer_id or select payment.customer_id).

answered Mar 23 '17 at 12:19

highvoltage

121
1
6

SQL: error implied with join, but I think it's in SELECT statement, thoughts?

2 Answers2