CODEWITHSUNDEEP
mysqlsqlgroup-by
tonix
tonix

Reputation: 6959

MySQL aggregate function against different groups which should be computed from an outermost GROUP BY?

I have the following table called table:

| id   | id_a | id_b  | date       | day_part | 
===============================================
 32043 |    0 |     0 | 2015-12-03 |    A     |
 31933 |    0 | 31933 | 2015-12-03 |    A     |
 31908 |    0 |     0 | 2015-12-03 |    A     | 
 31384 |    0 | 31384 | 2015-11-30 |    M     |
 31335 |    0 |     0 | 2015-11-30 |    M     |
 31303 |    0 | 31303 | 2015-11-30 |    M     |
 31302 |    0 |     0 | 2015-11-30 |    M     |  
 31300 |    0 |     0 | 2015-11-30 |    M     |
 31223 |    0 |     0 | 2015-12-03 |    A     |
 31221 |    0 | 31221 | 2015-11-30 |    M     |
 32037 |    0 | 32037 | 2015-12-03 |    A     |
 31301 | 31301|     0 | 2015-11-30 |    M     |
 31901 | 31301|     0 | 2015-11-30 |    M     |

id is a unique id, day_part refers to the part of the day ('A' means 'afternoon', 'M' means 'morning').

Lets say I have a range of dates between 2015-11-30 and 2015-12-06.

For each day I need to find the COUNT of the id fields for each day_part:

|  date     | M_ids_count | A_ids_count |
=========================================
 2015-11-30 | 8           | 0
 2015-12-03 | 0           | 5

I can easily do it with:

SELECT 
    date,
    SUM(CASE WHEN day_part = 'M' THEN 1 ELSE 0 END) AS M_ids_count,
    SUM(CASE WHEN day_part = 'A' THEN 1 ELSE 0 END) AS A_ids_count
FROM table
WHERE date BETWEEN '2015-11-30' AND '2015-12-06'
AND day_part IN ("M", "A")
GROUP BY date;

The tricky part is now:

I need to return the following result:

|  date     | M_ids_count | A_ids_count | M_trips_count | A_trips_count |
=========================================================================
 2015-11-30 | 8           | 0           |        7      |      0 
 2015-12-03 | 0           | 5           |        0      |      5

We already now how to compute M_ids_count and A_ids_count.

M_trips_count related to that nth date and that nth day_part and it's computed in the following way:

The same goes for A_trips_count and A_trips_count but it is related to that date in the afternoon day_part.

So in our case, if you look at the table you will see two fields with id_a = 31301 (having id 31301 and 31901) with the same date and day_part. This will count 1 as it meets the requirements of Condition 1.

This 1 should then be added to the COUNT of the records that meet Condition 2 for the same date and day_part: they are 3 (with ids 31384, 31303, 31221). Here we have a total of 4.

This 4 should then be added to the COUNT of the records that meet Condition 3 for the same date and day_part: they are 3 (ids -> 31335, 31302 and 31300).

Hence we get 7 which is the value for M_trips_count for the day 2015-11-30 and the M day_part (i.e. the morning). The same computation should go for the A_trips_count for the same date but it's related to the afternoon.

Then the same should go for the other dates in the range between 2015-11-30 and 2015-12-06 (in our case just for the date 2015-12-03 cause there aren't other dates). Same computation for M_trips_count and A_trips_count as for 2015-11-30.

Now, can/how can I do it using just one SQL query?

The result I need is (the same as above):

   |  date     | M_ids_count | A_ids_count | M_trips_count | A_trips_count |
    =========================================================================
     2015-11-30 | 8           | 0           |        7      |      0 
     2015-12-03 | 0           | 5           |        0      |      5

Thanks for the attention!

Upvotes: 0

Views: 31

Answers (1)

Simhachalam Gulla
Simhachalam Gulla

Reputation: 998

Try the below code mostly will work.

SELECT t.dp AS date_part,
    SUM(t.M_ids_count) AS M_ids_count, 
    SUM(t.A_ids_count) AS A_ids_count,
    SUM(IF(t.M_trip_count = 'M_NULL', 0, (IF(t.M_trip_count = 'M_SAME', t.M_ids_count, 1)))) AS M_trip_count,
    SUM(IF(t.A_trip_count = 'A_NULL', 0, (IF (t.A_trip_count = 'A_SAME', t.A_ids_count, 1)))) AS A_trip_count
FROM (
    SELECT 
        date_part AS dp,
        SUM(CASE WHEN day_part = 'M' THEN 1 ELSE 0 END) AS M_ids_count,
        SUM(CASE WHEN day_part = 'A' THEN 1 ELSE 0 END) AS A_ids_count,
        (CASE WHEN (day_part = 'M') THEN (IF(((id_a = 0 AND id_b = 0) OR (id_a = id_b)), 'M_SAME', 'M_DIFF')) ELSE 'M_NULL' END) AS M_trip_count,
        (CASE WHEN (day_part = 'A') THEN (IF(((id_a = 0 AND id_b = 0) OR (id_a = id_b)), 'A_SAME', 'A_DIFF')) ELSE 'A_NULL' END) AS A_trip_count
    FROM timer
    WHERE date_part BETWEEN '2015-11-30' AND '2015-12-06' AND day_part IN ("M", "A")
    GROUP BY id_a,id_b,M_trip_count,A_trip_count
) AS t 
GROUP BY t.dp

This will work as per below table structure, which you have specified in above table.

CREATE TABLE IF NOT EXISTS `timer` (
  `id` int(11) NOT NULL,
  `id_a` int(11) NOT NULL,
  `id_b` int(11) NOT NULL,
  `date_part` date NOT NULL,
  `day_part` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `timer` (`id`, `id_a`, `id_b`, `date_part`, `day_part`) VALUES
(32043, 0, 0, '2015-12-03', 'A'),
(31933, 0, 31933, '2015-12-03', 'A'),
(31908, 0, 0, '2015-12-03', 'A'),
(31384, 0, 31384, '2015-11-30', 'M'),
(31335, 0, 0, '2015-11-30', 'M'),
(31303, 0, 31303, '2015-11-30', 'M'),
(31302, 0, 0, '2015-11-30', 'M'),
(31300, 0, 0, '2015-11-30', 'M'),
(31223, 0, 0, '2015-12-03', 'A'),
(31221, 0, 31221, '2015-11-30', 'M'),
(32037, 0, 32037, '2015-12-03', 'A'),
(31301, 31301, 0, '2015-11-30', 'M'),
(31901, 31301, 0, '2015-11-30', 'M');

Upvotes: 1

Related Questions