CODEWITHSUNDEEP
mysqlgroup-bymax
Ej DEV
Ej DEV

Reputation: 202

Mysql Grouping of Max field by month on a set of Fields

I would like to generate a SQL to list to display the maximum tally count based on enumerated group of values on a monthly basis. As this would be useful for analytics based algorithm in displaying the total impressions of a particular data type.

Please check my sample table: 

CREATE TABLE IF NOT EXISTS `company_attendance_tally` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_date` datetime NOT NULL,
  `sick_type` enum('VACATION','SICK','MATERNITY') COLLATE utf8_unicode_ci NOT NULL,
  `leave_count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `start_date` (`start_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=32 ;

--
-- Dumping data for table `company_attendance_tally`
--

INSERT INTO `company_attendance_tally` (`id`, `start_date`, `sick_type`, `leave_count`) VALUES
(1, '2013-03-01 16:58:44', 'VACATION', 5),
(2, '2013-03-15 10:44:35', 'SICK', 43),
(3, '2013-03-21 17:03:33', 'MATERNITY', 44),
(4, '2013-03-07 23:01:30', 'MATERNITY', 10),
(5, '2013-03-22 17:07:07', 'MATERNITY', 1),
(6, '2013-03-08 19:33:04', 'VACATION', 40),
(7, '2013-03-17 12:27:00', 'MATERNITY', 15),
(8, '2013-03-03 23:26:48', 'SICK', 11),
(9, '2013-03-05 02:16:37', 'MATERNITY', 41),
(10, '2013-03-20 12:04:28', 'MATERNITY', 18),
(11, '2013-03-18 02:10:00', 'MATERNITY', 1),
(12, '2013-03-03 09:47:02', 'MATERNITY', 19),
(13, '2013-03-22 10:17:52', 'MATERNITY', 25),
(14, '2013-03-03 19:41:52', 'VACATION', 10),
(15, '2013-03-02 19:28:41', 'SICK', 39),
(16, '2013-03-01 20:45:26', 'SICK', 42),
(17, '2013-03-26 23:52:16', 'MATERNITY', 29),
(18, '2013-03-29 14:10:58', 'SICK', 44),
(19, '2013-03-27 03:11:40', 'MATERNITY', 12),
(20, '2013-03-06 18:38:28', 'MATERNITY', 30),
(21, '2013-03-07 20:49:14', 'VACATION', 27),
(22, '2013-03-13 11:38:45', 'VACATION', 14),
(23, '2013-03-02 19:13:31', 'SICK', 2),
(24, '2013-03-01 10:08:18', 'SICK', 27),
(25, '2013-03-20 01:56:38', 'VACATION', 3),
(26, '2013-03-04 21:02:05', 'SICK', 7),
(27, '2013-03-17 00:47:17', 'MATERNITY', 36),
(28, '2013-03-04 08:12:56', 'VACATION', 5),
(29, '2013-03-18 08:50:57', 'SICK', 34),
(30, '2013-03-26 02:20:58', 'VACATION', 20),
(31, '2013-03-27 10:27:00', 'SICK', 21);

http://sqlfiddle.com/#!2/bbd1e3

I would like to display a similar output below based on the above scenario:

month|  day       |  sick_type  |      leave_count |
-----------------------------------------------------
    3|    08      |   VACATION  | 40  
    3|    29      |   SICK      | 29
    3|    21      |   MATERNITY |  44
and so on so forth...
    4|    ...     |   MATERNITY |  ..
    4|    ...     |   SICK      |  ..
    4|    ...     |   VACATION  |  ..
    5|    ...     |   MATERNITY |  ..
    5|    ...     |   SICK      |  ..
    5|    ...     |   VACATION  |  ..

Upvotes: 0

Views: 81

Answers (3)

Strawberry
Strawberry

Reputation: 33945

If you want to do it 'by-the-book', consider the following...

SELECT x.* 
  FROM company_attendance_tally x
  JOIN 
     ( SELECT MONTH(start_date) start_month
            , sick_type
            , MAX(leave_count) max_leave_count
         FROM company_attendance_tally
        GROUP 
           BY MONTH(start_date)
            , sick_type
     ) y
    ON y.start_month = MONTH(x.start_date)
   AND y.sick_type = x.sick_type
   AND y.max_leave_count = x.leave_count;

Upvotes: 1

peterm
peterm

Reputation: 92795

If I understand correctly what you want you can do following by leveraging non-standard MySQL GROUP BY extension

SELECT MONTH(start_date) month, 
       DAYOFMONTH(start_date) day,
       sick_type,
       leave_count
  FROM 
(
  SELECT start_date, sick_type, leave_count
    FROM company_attendance_tally
   WHERE start_date >= '2013-01-01'
     AND start_date <  '2014-01-01'
   ORDER BY MONTH(start_date), sick_type, leave_count DESC
) q
 GROUP BY MONTH(start_date), sick_type

Note: Month values alone (without a year values) in the resultset make sense only if you limit the resultset by one year boundaries (see WHERE clause).

Output:

| MONTH | DAY | SICK_TYPE | LEAVE_COUNT |
|-------|-----|-----------|-------------|
|     3 |   8 |  VACATION |          40 |
|     3 |  29 |      SICK |          44 |
|     3 |  21 | MATERNITY |          44 |

Here is SQLFiddle demo

Upvotes: 1

voodoo417
voodoo417

Reputation: 12101

Use this:

SELECT DAY( start_date ) AS d,
       MONTH( start_date ) AS mon, 
       sick_type,
       MAX( leave_count ) AS leave_count
FROM `company_attendance_tally`
GROUP BY mon, sick_type

Upvotes: 1

Related Questions