Mysql query with last 30 day results showing totals per day

Question

Mysql query with last 30 day results showing totals per day

Asked 8 years, 2 months ago

Viewed 275 times

0

I have a query where you get results from the last few days, I need the results to be shown per day, I used the group by , but it hasn’t worked yet, as you can see below.

 resultado esperado:
 2016-09-15 07:49:45    1
 2016-09-14 07:49:45    16
 2016-09-13 07:49:45    5

And I also need to take the hours of the result leaving only the date.

SELECT
      o.data_abertura DATA,
      COUNT(o.numero)   TOTAL
        FROM
            ocorrencias AS o
                LEFT JOIN
            sistemas AS a ON a.sis_id = o.sistema
                LEFT JOIN
            localizacao AS l ON l.loc_id = o.local
                LEFT JOIN
            instituicao AS i ON i.inst_cod = o.instituicao
                LEFT JOIN
            usuarios AS u ON u.user_id = o.operador
                LEFT JOIN
            usuarios AS ua ON ua.user_id = o.aberto_por
                LEFT JOIN
            `status` AS s ON s.stat_id = o.status
                LEFT JOIN
            status_categ AS stc ON stc.stc_cod = s.stat_cat
                LEFT JOIN
            problemas AS p ON p.prob_id = o.problema
                LEFT JOIN
            sla_solucao AS sls ON sls.slas_cod = p.prob_sla
                LEFT JOIN
            prioridades AS pr ON pr.prior_cod = l.loc_prior
                LEFT JOIN
            sla_solucao AS slr ON slr.slas_cod = pr.prior_sla
                LEFT JOIN
            script_solution AS sol ON sol.script_cod = o.oco_script_sol
                LEFT JOIN
            prior_atend AS prioridade_atendimento ON prioridade_atendimento.pr_cod = o.oco_prior
    WHERE
          o.data_abertura BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
           AND  a.sistema IN ('Ti','Eletrica','Terceirizados','Maquinarios')
           GROUP BY o.data_abertura

Upshot:

2016-09-15 07:49:45 1
2016-09-14 23:07:58 1
2016-09-14 23:04:50 1
2016-09-14 21:01:52 1
2016-09-14 18:49:58 1
2016-09-14 18:21:45 1
2016-09-14 18:18:44 1
2016-09-14 15:42:27 1
2016-09-14 12:31:52 1
2016-09-14 10:55:09 1
2016-09-14 10:52:52 1
2016-09-14 10:49:16 1
2016-09-14 10:45:38 1
2016-09-14 10:43:04 1
2016-09-14 10:24:48 1
2016-09-14 09:41:31 1
2016-09-14 07:20:28 1
2016-09-13 18:14:59 1
2016-09-13 16:53:04 1
2016-09-13 16:42:13 1
2016-09-13 14:07:07 1
2016-09-13 14:04:57 1
2016-09-12 21:24:57 1
2016-09-12 19:10:48 1
2016-09-12 19:08:07 1
2016-09-12 17:49:16 1
2016-09-12 17:45:29 1
2016-09-12 17:24:40 1
2016-09-12 17:23:04 1
2016-09-12 17:02:09 1
2016-09-12 16:10:54 1
2016-09-12 15:45:49 1
2016-09-12 15:19:40 1
2016-09-12 15:14:31 1
2016-09-12 14:38:55 1
2016-09-12 13:53:14 2
2016-09-11 12:23:00 1
2016-09-10 22:20:19 1
2016-09-10 08:44:10 1
2016-09-10 08:39:37 1
2016-09-10 08:35:09 1
2016-09-09 15:50:56 1
2016-09-09 14:13:30 1
2016-09-09 11:22:56 1
2016-09-09 10:35:25 1
2016-09-09 08:50:40 1
2016-09-08 17:24:18 1
2016-09-08 17:09:13 1
2016-09-08 16:59:57 1
2016-09-08 14:07:09 1
2016-09-08 14:05:33 1
2016-09-08 14:03:57 1
2016-09-08 13:27:16 1
2016-09-08 11:29:54 1
2016-09-08 11:15:03 1
2016-09-08 10:46:24 1
2016-09-08 10:42:42 1
2016-09-08 10:26:41 1
2016-09-08 10:24:43 1
2016-09-08 09:33:12 1
2016-09-08 08:55:24 1
2016-09-08 08:24:06 1
2016-09-07 14:43:19 1
2016-09-07 14:26:36 1
2016-09-07 14:24:02 1
2016-09-07 13:59:57 1
2016-09-07 13:33:45 1
2016-09-06 14:30:54 1
2016-09-06 14:27:14 1
2016-09-06 13:45:02 1
2016-09-06 12:57:30 1
2016-09-06 11:42:33 1
2016-09-06 11:25:15 1
2016-09-06 11:07:41 1
2016-09-06 09:12:45 1
2016-09-06 08:27:32 1
2016-09-06 08:16:25 1
2016-09-06 08:09:13 1
2016-09-05 20:22:05 1
2016-09-05 20:16:50 1
2016-09-05 18:06:57 1
2016-09-05 15:36:51 1
2016-09-05 15:03:15 1
2016-09-05 14:25:24 1
2016-09-05 14:22:46 1
2016-09-05 14:19:09 1
2016-09-05 11:25:41 1
2016-09-05 10:50:46 1
2016-09-05 08:33:06 1
2016-09-04 14:22:47 1
2016-09-03 11:27:41 1
2016-09-03 11:26:26 1
2016-09-03 11:24:48 1
2016-09-02 18:03:29 1
2016-09-02 17:07:05 1
2016-09-02 14:34:02 1
2016-09-02 11:18:14 1
2016-09-02 11:09:29 1
2016-09-02 11:05:03 1
2016-09-01 23:03:36 1
2016-09-01 19:03:50 1
2016-09-01 18:46:27 1
2016-09-01 17:28:24 1
2016-09-01 16:52:47 1
2016-09-01 14:23:35 1
2016-09-01 11:44:42 1
2016-09-01 11:28:34 1
2016-09-01 10:33:21 1
2016-09-01 08:30:29 1
2016-09-01 08:24:29 1
2016-08-31 18:03:10 1
2016-08-31 16:35:15 1
2016-08-31 14:05:58 1
2016-08-31 09:43:13 1
2016-08-31 08:55:41 1
2016-08-30 17:26:48 1
2016-08-30 15:53:12 1
2016-08-30 09:52:27 1
2016-08-30 08:19:47 1
2016-08-30 08:16:38 1
2016-08-29 21:04:25 1
2016-08-29 18:52:58 1
2016-08-29 18:42:00 1
2016-08-29 18:31:13 1
2016-08-29 16:29:45 1
2016-08-29 12:17:47 1
2016-08-29 12:15:08 1
2016-08-29 10:03:07 1
2016-08-29 10:00:20 1
2016-08-29 09:20:33 1
2016-08-29 07:59:46 1
2016-08-28 13:36:14 1
2016-08-26 20:15:04 1
2016-08-26 18:07:54 1
2016-08-26 17:11:41 1
2016-08-26 17:08:35 1
2016-08-26 15:20:53 1
2016-08-26 15:12:34 1
2016-08-26 15:09:33 1
2016-08-26 15:07:08 1
2016-08-26 15:04:49 1
2016-08-26 12:46:08 1
2016-08-26 12:18:53 1
2016-08-26 12:17:30 1
2016-08-26 11:49:14 1
2016-08-26 11:24:31 1
2016-08-26 11:23:01 1
2016-08-25 18:35:39 1
2016-08-25 18:24:31 1
2016-08-25 18:14:12 1
2016-08-25 18:11:23 1
2016-08-25 17:46:45 1
2016-08-25 16:59:13 1
2016-08-25 16:18:10 1
2016-08-25 15:59:45 1
2016-08-25 15:53:37 1
2016-08-25 11:26:38 1
2016-08-25 11:22:55 1
2016-08-25 10:57:42 1
2016-08-25 10:24:45 1
2016-08-25 10:09:05 1
2016-08-25 09:41:21 1
2016-08-24 17:35:30 1
2016-08-24 15:38:03 1
2016-08-24 15:34:28 1
2016-08-23 20:05:51 1
2016-08-23 20:03:57 1
2016-08-23 17:24:26 1
2016-08-23 17:20:56 1
2016-08-23 15:06:28 1
2016-08-23 12:52:14 1
2016-08-23 12:19:23 1
2016-08-22 18:08:34 1
2016-08-22 15:47:49 1
2016-08-22 15:45:54 1
2016-08-22 15:43:24 1
2016-08-22 15:36:21 1
2016-08-22 14:55:18 1
2016-08-22 14:21:03 1
2016-08-22 13:12:36 1
2016-08-22 09:35:31 1
2016-08-22 08:51:50 1
2016-08-20 16:17:20 1
2016-08-20 12:26:47 1
2016-08-20 11:21:58 1
2016-08-20 08:40:51 1
2016-08-19 15:40:53 1
2016-08-19 13:56:34 1
2016-08-19 13:52:11 2
2016-08-19 11:32:34 1
2016-08-19 11:05:13 1
2016-08-19 10:20:28 1
2016-08-19 09:22:16 1
2016-08-18 15:51:11 1
2016-08-18 12:25:31 1
2016-08-18 12:24:09 1
2016-08-17 11:43:30 1
2016-08-17 10:21:09 1
2016-08-17 09:20:54 1
2016-08-16 15:48:46 1
2016-08-16 15:37:32 1
2016-08-16 15:36:57 1
2016-08-16 15:26:24 1
2016-08-16 13:24:41 1
2016-08-16 11:24:08 1
2016-08-16 11:14:09 1

1 answer

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.

by osmarcf • **337** points · Answer 1 · 2016-09-15T13:30:01+00:00

By grouping by data_abertura (which must be a Datetime), it compares the full value of Datetime (day, month, year, hour, minute, second, era, Timezone, etc).

In this case the most interesting is to group only by year, month and day: GROUP BY YEAR(o.data_abertura), MONTH(o.data_abertura), DAY(o.data_abertura)

I hope I’ve helped!