[FIXED] Return the most popular day for bookings

Issue

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.

Solution

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

Answered By - Derviş Kayımbaşıoğlu