1

I have a database and want to select from mysql a result which could not succeed after one month please help me it would be really appreciated!

My Database Table looks like below and Name = Customer

Id  customer_id     customer_name       Date            Books_bought
1           1           Alex            08.06.2017      HarryPotter1 
2           1           Alex            08.06.2017      Harry Potter2
3           1           Alex            09.06.2017      PHP 7
4           2           Sam             10.06.2017      SQL 

I want to display the following:

  1. What books Alex bought on 08.06.2017 - Harry Potter1 and Harry Potter 2
  2. In another table row that Alex on 09.06.2017 bought only PHP 7
  3. In another table row another customer Sam on 10.06.2017 bought SQL

However, the result return all books for one customer Alex which has customer_id=1 and the reset users result are the same My current html and php Table looks like below:

Id  customer_id    customer_name        Date            Books_bought

1           1           Alex            08.06.2017      HarryPotter1 
                                                        Harry Potter2
                                                        PHP 7
                                                        SQL
---------------------------------------------------------------------------
2          1            Alex            09.06.2017      HarryPotter1 
                                                        Harry Potter2
                                                        PHP 7
                                                        SQL

-------------------------------------------------------------------------
3          2            Sam             10.06.2017      HarryPotter1 
                                                        Harry Potter2
                                                        PHP 7
                                                        SQL
  • 1
    Do you have the query that you used to get your current output? – Anthony Genovese Jun 9 '17 at 19:18
1

You seem to want to GROUP BY customer and date; and I guess that you want to concatenate the books.

This would do it:

 SELECT
     customer_id, customer_name, `Date`, 
     GROUP_CONCAT(`Books_bought` ORDER BY `Books_bought` SEPARATOR ', ') AS books
 FROM
     t
 GROUP BY
     customer_id, customer_name , `Date`
 ORDER BY
     `Date`, customer_id, customer_name ;

 customer_id | customer_name | Date       | books
 ----------: | :------------ | :--------- | :--------------------------
           1 | Alex          | 08.06.2017 | Harry Potter2, HarryPotter1
           1 | Alex          | 09.06.2017 | PHP 7
           2 | Sam           | 10.06.2017 | SQL
 

Check:

dbfiddle here

  • Dear Joanolo, I really thank you for the perfect answer I have ever received but your solution only works in maria db but I use phpmyadmin sql and I recieve this error: – sam Jun 9 '17 at 22:08
  • SQL query: Documentation SELECT customer_id, customer_name, Date, GROUP_CONCAT(Books_bought ORDER BY Books_bought SEPARATOR ', ') AS books FROM t GROUP BY customer_id, Date ORDER BY Date, customer_id LIMIT 0, 25 MySQL said: Documentation #1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cc.t.customer_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – sam Jun 9 '17 at 22:08
  • if you please correct the query it would be really apperciated, Thank you very much you are the best! – sam Jun 9 '17 at 22:10
  • Done, added customer_name to the GROUP BY... now that's SQL standardish... – joanolo Jun 9 '17 at 22:27
  • I knew it, you can do it, you are the best! i don't know how to thank you best of Luck! really apperciated! – sam Jun 9 '17 at 23:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.