Mysql - Calculate Times per month - organise result

Question

I'm extracting data from sql by the following SQL Query

SELECT DISTINCT
  SEC_TO_TIME(SUM(TIME_TO_SEC(transics_bco.arab))) AS arab,
  transics_bco.plate
FROM transics_bco
WHERE transics_bco.extdate BETWEEN '2019-12-01' AND '2019-12-31'
GROUP BY transics_bco.plate

This gives me the following result:

-----------------------
| ARAB      | Plate   |
-----------------------
| 178:44:43 | 1ABC123 |
| 156:23:44 | 1DEF456 |
-----------------------

Is it possible to get the result shown as this;

---------------------------------------------------------------
| Plate   | December 19 | January 20 | February 20 | March 20 |
--------------------------------------------------------------- 
| 1ABC123 | 178:44:43   | 120:34:56  | ...         | ...      |
| 1DEF456 | 156:23:44   | 102:34:54  | ...         | ...      |
| 1GHI789 | 111:22:33   | 156:35:35  | ...         | ...      |
---------------------------------------------------------------

SQL Sample: transics_bco

----------------------------------------
| id | plate   | arab     | extdate    |
----------------------------------------
| 1  | 1ABC123 | 00:14:23 | 2019-12-01 |
| 2  | 1ABC123 | 00:10:20 | 2019-12-03 |
| 3  | 1ABC123 | 00:45:06 | 2019-12-07 |
| 4  | 1ABC123 | 00:54:45 | 2020-01-02 |
| 5  | 1ABC123 | 00:26:10 | 2020-01-03 |
| 6  | 1ABC123 | 00:43:28 | 2020-01-04 |
----------------------------------------

Would greatly appreciate pointers in the good direction

Adding db describe as requested in the comments;

- Adding describe of Transics_bco as requested in the comments

===================================================================================================================================================================================================================================================================================================================
|                      Field                       |                       Type                       |                       Null                       |                       Key                        |                     Default                      |                      Extra                       |
===================================================================================================================================================================================================================================================================================================================
|                        id                        |                     int(11)                      |                        NO                        |                       PRI                        |                       null                       |                  auto_increment                  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                      plate                       |                   varchar(255)                   |                        NO                        |                                                  |                       null                       |                                                  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                       arab                       |                       time                       |                        NO                        |                                                  |                     00:00:00                     |                                                  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                     extdate                      |                       date                       |                        NO                        |                                                  |                       null                       |                                                  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

It might be helpful to provide the schema of both tables. Run `describe transics_soap;` and `describe transics_bco;`. — psyklopz, Mar 09 '20 at 00:43
@psyklopz: suppressed the soap db, wasn't really necessary. Added describe above — Erwin Dubois, Mar 09 '20 at 01:04
It might not exactly be what you are looking for, but I would consider grouping by plate, MONTH(), and YEAR(). It would put different months in different rows, (not columns, as asked) but it keeps everything contained in a single query. — psyklopz, Mar 09 '20 at 01:41
Otherwise, I think the only way to accomplish what you are doing is with a UNION query. That's something I've always seem as an anti-pattern, and so I've avoided. I don't know enough about UNIONs to provide an answer. — psyklopz, Mar 09 '20 at 01:42
If I count it correctly, the time difference between `| 1 | 1ABC123 | 00:14:23 | 2019-12-01 |` and `| 3 | 1ABC123 | 00:45:06 | 2019-12-07 |` is actually `144:30:43` but your result return `178:44:43` .. it doesn't match with your example data given.. any clarification on that or it's just not complete? If so, please give us the full example that will return `178:44:43` result. You should just make sure the result and example data provided are a match.. otherwise it will be confusing.. thanks — FanoFN, Mar 09 '20 at 02:01
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Mar 09 '20 at 07:12

score 1 · Accepted Answer · edited May 12 '20 at 19:28

SELECT
  plate,
  december19,
  january20,
  february20,
  march20 
FROM
  (
    SELECT DISTINCT
      a.plate,
      (
        CASE
          WHEN
            Month(a.extdate) = 12 
            AND Year(a.extdate) = 2019 
          THEN
            Sec_to_time(Sum(Time_to_sec(a.arab))) 
        END
      )
      AS 'December19', 
      (
        CASE
          WHEN
            Month(a.extdate) = 1 
            AND Year(a.extdate) = 2020 
          THEN
            a.arab 
        END
      )
      AS 'January20, (CASE WHEN MONTH(a.extDate)=2 AND YEAR(a.extDate)=2020 THEN SEC_TO_TIME(SUM(TIME_TO_SEC(a.arab))) END) AS 'february20', (CASE WHEN MONTH(a.extDate)=3 AND YEAR(a.extDate)=2020 THEN SEC_TO_TIME(SUM(TIME_TO_SEC(a.arab))) END) AS 'march20', FROM transics_bco a WHERE transics_bco.extdate BETWEEN '2019-12-01' AND '2019-12-31' 
    GROUP BY
      transics_bco.plate
  )
  T 
GROUP BY
  T.plate;

Mysql - Calculate Times per month - organise result

1 Answers1