0

Hi all this is my query - if I run this I get multiple customer ids but I just want to select one customer id

SELECT payments.customer_id,
       payments.reason,
       payments.date
FROM payments  
WHERE reason like '%error%' 
ORDER BY payments.date DESC

This is the error message I get if i try to use a group by

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'reason' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (errno 1055) (sqlstate 42000)

anca
  • 19
  • 4
  • Could you add some data, and your query doesn't contain any Group By Clause, but data so that we can see what result you get and where it comes from is necessary. – nbk Mar 20 '20 at 14:38
  • that is always a problem, so you have to do some work and anonymize it. – nbk Mar 20 '20 at 14:46
  • i think i need a group by with customer_id but it keeps on throwing an error – anca Mar 20 '20 at 14:55
  • It is hard to gues without any data, but grouping by customer_id is a good start and try an aggregathion for the rest of the columns like Group_:concat – nbk Mar 20 '20 at 14:55

2 Answers2

0
SELECT DISTINCT payments.customer_id, 
                payments.reason,
                payments.date FROM payments
WHERE reason like '%error%' 
ORDER BY payments.date DESC

DISTINCT will solve this for you

anca
  • 19
  • 4
Badmous
  • 95
  • 1
  • 5
  • thanks Arun and Badmous - I tried to use distinct already but I still get multiple customer ids . I think this might happen because declined reason appears several time for the same customer. Im trying to get one error for one customer_id – anca Mar 20 '20 at 14:36
0

Do you need below - 

SELECT p.customer_id,
       p.reason,
       MAX(p.date) max_date
FROM payments p
WHERE p.reason like '%error%' 
GROUP BY p.customer_id,
         p.reason
ORDER BY max_date DESC
anca
  • 19
  • 4
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40