phpmyadmin Question posted in
The official documentation can be found here.

Return the most popular day for bookings – Phpmyadmin

I am trying to write a MySQL query that returns the most popular day of the week for bookings to take place.

I am unsure of my issue here. MySQL returned an error saying there is a syntax error, but because of the complexity of the query (for me anyway) I am unable to see the cause.

Error reads:

“#1064 – You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use
near ”tbl_booking’ GROUP BY DATE_FORMAT(‘tbl_booking.booking_date’,
‘%W’) ORDER BY ‘ at line 2″

    SELECT DATE_FORMAT('tbl_booking.booking_date', '%W'), COUNT(DATE_FORMAT('tbl_booking.booking_date', '%W')) AS 'mostpopularday'
FROM 'tbl_booking'
GROUP BY DATE_FORMAT('tbl_booking.booking_date', '%W')
ORDER BY 'mostpopularday' DESC
LIMIT 1

I have used the DATE_FORMAT() function previously, but never for sorting data. I am quite new to MySQL/PhpMyAdmin, so any help is greatly appreciated. Thanks.

Tags:

2

Answers


  1. 0

    your query looks ok, but you have syntax errors. You shouldn’t be using ' for column and table names. In mysql you can use ` for quoting tablenames and column names

    SELECT 
  DATE_FORMAT(tbl_booking.booking_date, '%W'), 
  COUNT(DATE_FORMAT(tbl_booking.booking_date, '%W')) AS mostpopularday
FROM tbl_booking
GROUP BY DATE_FORMAT(tbl_booking.booking_date, '%W')
ORDER BY mostpopularday DESC
LIMIT 1
    Login or Signup to reply.
  2. 0

    Your ' delimiters around your table and column names are incorrect.

    For best results get rid of the delimiters altogether, like this.

     SELECT DATE_FORMAT(tbl_booking.booking_date, '%W'), 
        COUNT(DATE_FORMAT(tbl_booking.booking_date, '%W')) AS mostpopularday
   FROM tbl_booking
  GROUP BY DATE_FORMAT(tbl_booking.booking_date, '%W')
  ORDER BY mostpopularday DESC
  LIMIT 1

    If you must keep them, they should look like this. Notice the backticks: `.

       SELECT DATE_FORMAT(`tbl_booking`.`booking_date`, '%W'), 
          COUNT(DATE_FORMAT(`tbl_booking`.`booking_date`, '%W')) AS mostpopularday
     FROM `tbl_booking`
    GROUP BY DATE_FORMAT(`tbl_booking`.`booking_date`, '%W')
    ORDER BY mostpopularday DESC
    LIMIT 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search