firasKoubaa firasKoubaa - 5 days ago 5
MySQL Question

MySQL : Group by Datetime (Hour - Day - Month - Year)

I have this form of table
enter image description here

i wanna Count insertions which have the type = "non_repondu" and group the results by 3 criteria.


1-client_id

2-queue_id

and

3- datetime : by hour , day , by month and by year


So far i have done this:

SELECT `client_id`,`queue_id`,`datetime`,COUNT(`type`)
AS nombre_appels_repondus
FROM `appels`
where `type`= "non_repondu"
group by `appels`.`client_id`,`appels`.`queue_id`,`appels`.`datetime


enter image description here


but like that i have just filtered by the full datetime column which results me
a table of every unique datetime line separately , how can i get result by hour
, day , month and year ???

Answer

Use DATE_FORMATE() in group By clause. Hope it should be solved your problem

SELECT
    `client_id`,
    `queue_id`,
    `datetime`,
    COUNT(`type`) AS nombre_appels_repondus
FROM
    `appels`
WHERE
    `type` = "non_repondu"
GROUP BY
    `appels`.`client_id`,
    `appels`.`queue_id`,
    DATE_FORMAT(
        `appels`.`datetime`,'%Y-%m-%d %H');

OR

If you want to add in your selected column list with day, month, year and hour then please try this one

SELECT
    `client_id`,
    `queue_id`,
    `datetime`,
    DATE_FORMAT(`appels`.`datetime`, '%Y-%m-%d') AS DAY,
    DATE_FORMAT(`appels`.`datetime`, '%Y-%m') AS MONTH,
    DATE_FORMAT(`appels`.`datetime`, '%Y') AS YEAR,
    DATE_FORMAT(`appels`.`datetime`, '%H') AS HOUR,
    COUNT(`type`) AS nombre_appels_repondus
FROM
    `appels`
WHERE
    `type` = "non_repondu"
GROUP BY
    `appels`.`client_id`,
    `appels`.`queue_id`,
    DATE_FORMAT(
        `appels`.`datetime`,
        '%Y-%m-%d %H'
    );
Comments