-1

I have more than 10,000+ lines/rows of data for a single day.

How can I know which date in a month has max row/lines? I want to find the trend of Max number of breaks every month.

enter image description here

steffen
  • 16,138
  • 4
  • 42
  • 81
Kiran
  • 11
  • You can use `group by` and `max` to achieve it. Please share what have you tried so far? – Jaydip Rakholiya Oct 03 '18 at 14:05
  • Possible duplicate of [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) – Jaydip Rakholiya Oct 03 '18 at 14:06
  • @Kiran Did this help? – steffen Oct 03 '18 at 19:21
  • @ Steffen i am getting error while running the query "select count(*) entries, year(date) y, month(date) m, day(date) d from dates group by y,m,d;" the error message shows - Msg 207, Level 16, State 1, Line 51 Invalid column name 'year'. Msg 207, Level 16, State 1, Line 51 Invalid column name 'month'. Msg 207, Level 16, State 1, Line 51 Invalid column name 'day'. – Kiran Oct 04 '18 at 11:41
  • @Kiran You didn't post your database schema, so I was making up a schema which should be similar to yours. Basically I have a column of type `datetime` and `year()`, `month()`, `day()` are mysql functions, see https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html. Of what type is your "Closing date" column? – steffen Oct 04 '18 at 12:38

1 Answers1

0

The problem is that you need to group by day (entries per day) and then group by month (maximum entries per day in a month). You can do that in one query, but I think there's no effective and easy way to achieve that since you can't use derived tables multiple times. Here's what I suggest:

Let's say you have a table like this:

MariaDB [test]> create table dates (date datetime, data int default null);
Query OK, 0 rows affected (0.02 sec)

Put in some rows:

MariaDB [test]> select * from dates;
+---------------------+------+
| date                | data |
+---------------------+------+
| 2018-10-03 00:00:00 | NULL |
| 2018-01-01 00:00:00 | NULL |
| 2018-01-02 00:00:00 | NULL |
| 2018-01-02 00:00:00 | NULL |
| 2018-01-02 00:00:00 | NULL |
| 2018-01-03 00:00:00 | NULL |
| 2018-01-03 00:00:00 | NULL |
| 2018-02-01 00:00:00 | NULL |
+---------------------+------+
8 rows in set (0.00 sec)

A sample group by (entries per day):

MariaDB [test]> select count(*) entries, year(date) y, month(date) m, day(date) d from dates group by y, m, d;
+---------+------+------+------+
| entries | y    | m    | d    |
+---------+------+------+------+
|       1 | 2018 |    1 |    1 |
|       3 | 2018 |    1 |    2 |
|       2 | 2018 |    1 |    3 |
|       1 | 2018 |    2 |    1 |
|       1 | 2018 |   10 |    3 |
+---------+------+------+------+
5 rows in set (0.00 sec)

From here it would get complicated. In order to circumvent that, we create a view:

MariaDB [test]> create view counts_per_day as (select count(*) entries, year(date) y, month(date) m, day(date) d from dates group by y, m, d);
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> select * From counts_per_day;
+---------+------+------+------+
| entries | y    | m    | d    |
+---------+------+------+------+
|       1 | 2018 |    1 |    1 |
|       3 | 2018 |    1 |    2 |
|       2 | 2018 |    1 |    3 |
|       1 | 2018 |    2 |    1 |
|       1 | 2018 |   10 |    3 |
+---------+------+------+------+
5 rows in set (0.00 sec)

With this view, the next part is much simpler, as it's a typical greatest-n-per-group problem:

MariaDB [test]> select entries, a.y, a.m, a.d from counts_per_day a inner join (select y, m, max(entries) max_entries from counts_per_day group by y, m) b on a.entries = max_entries and a.y = b.y and a.m = b.m;
+---------+------+------+------+
| entries | y    | m    | d    |
+---------+------+------+------+
|       3 | 2018 |    1 |    2 |
|       1 | 2018 |    2 |    1 |
|       1 | 2018 |   10 |    3 |
+---------+------+------+------+
3 rows in set (0.00 sec)
steffen
  • 16,138
  • 4
  • 42
  • 81