-2

The query below gives me top 3 services for 6th June 2021.

SELECT products_name AS product,
       COUNT(products_name) AS NumberOfBookings
FROM bookings
WHERE date = "06-06-2021"
GROUP BY products_name,
ORDER BY NumberOfBookings DESC
LIMIT 3;

I want to repeat this for each day of June.

How do I do that? I was thinking of iterating over a date range but that is not working. Is there a way to do it with some nested queries or group by?

MIRAU
  • 25
  • 5
  • I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. –  Jun 07 '21 at 15:55

1 Answers1

1

You can use window functions with aggregation. Something like this:

SELECT b.*
FROM (SELECT date, products_name AS product,
             COUNT(*) AS NumberOfBookings,
             ROW_NUMBER() OVER (PARTITION BY date ORDER BY COUNT(*) DESC) as seqnum
      FROM bookings
      WHERE date >= '2021-06-01' AND date < '2021-07-01'
      GROUP BY products_name, date
     ) b
WHERE seqnum <= 3
ORDER BY Date, seqnum;

Note: This uses standard date formats for the date constants.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This only works on SQL server. In regular mysql, it doesn't. – MIRAU Jun 09 '21 at 14:39
  • @MIRAU . . . This is *Standard SQL* (well, except for the date constants). And it *does* work in MySQL. Here is a db<>fiddle showing that the syntax works: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=338370796743761dccfcdb6355c4b950. – Gordon Linoff Jun 09 '21 at 14:54
  • I see. I only said it only runs on SQL server etc. and not MYSQL based on this post: https://stackoverflow.com/questions/37153109/partition-by-not-working Anyways, I have two databases one is mysql and other is postgresql. The above query works perfectly fine on postgresql one but when I try to run it on mysql db, it shows this error: check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY Date(dDate) ORDER BY COUNT(*) ' at line 7 – MIRAU Jun 09 '21 at 16:40
  • 1
    @MIRAU . . . You are running on an old version of MySQL. – Gordon Linoff Jun 09 '21 at 17:09