CODEWITHSUNDEEP
mysqldatetime
firasKoubaa
firasKoubaa

Reputation: 6867

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 ???

Upvotes: 2

Views: 6744

Answers (3)

Sohail Ahmed
Sohail Ahmed

Reputation: 1190

SELECT 
YEAR(datetime), MONTH(datetime), DAY(datetime), HOUR(datetime),
client_id, queue_id, COUNT(type) AS nombre_appels_repondus
FROM appels
WHERE type = "non_repondu"
GROUP BY YEAR(datetime), MONTH(datetime), DAY(datetime), HOUR(datetime), 
client_id, queue_id;

Upvotes: 0

Faisal
Faisal

Reputation: 4765

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'
    );

Upvotes: 4

baao
baao

Reputation: 73261

You need to be more exact with your group by statement

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`,
    YEAR(`appels`.`datetime`) ,
    MONTH(`appels`.`datetime`),
    DAY(`appels`.`datetime`),
    HOUR(`appels`.`datetime`)

Upvotes: 0

Related Questions