Return the most popular day for bookings

Question

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.

Good work with your first question! Hope to see more from you! — O. Jones, Feb 14 '19 at 22:43
https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql — Don't Panic, Feb 14 '19 at 22:45

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-02-14T22:44:58.740

1

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

edited Feb 14 '19 at 22:44

answered Feb 14 '19 at 22:39

Derviş Kayımbaşıoğlu

28,492
4
50
72

Thank you! You're correct, there was no need for the quotations. Query works as intended now. – DonalC93 Feb 15 '19 at 17:41
1

you are welcome! if this answer is helpful for you, please consider to up-vote and/or mark it as answer. – Derviş Kayımbaşıoğlu Feb 16 '19 at 11:36

score 1 · Answer 2 · answered Feb 14 '19 at 22:42

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

Thank you! The first one works perfectly, as intended. – DonalC93 Feb 15 '19 at 17:42 — DonalC93, Feb 15 '19 at 17:42

Return the most popular day for bookings

2 Answers2