2

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

Community
  • 1
  • 1
firasKoubaa
  • 6,439
  • 25
  • 79
  • 148

3 Answers3

4

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'
    );
Faisal
  • 4,591
  • 3
  • 40
  • 49
  • 1
    #1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'asterisk_bi.appels.datetime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – firasKoubaa Nov 30 '16 at 15:03
  • follow this one http://stackoverflow.com/questions/37951742/1055-expression-of-select-list-is-not-in-group-by-clause-and-contains-nonaggr. hope it should be solve your problem – Faisal Nov 30 '16 at 15:09
  • i working on a distant server which i cannot modify , but i solver it by doing this : ANY_VALUE(`datetime`), DATE_FORMAT(ANY_VALUE(`appels`.`datetime`), '%d') AS DAY, (you can update your answer to eventuals visitors) – firasKoubaa Nov 30 '16 at 15:20
0

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`)
baao
  • 71,625
  • 17
  • 143
  • 203
  • i got this error :#1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'asterisk_bi.appels.datetime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – firasKoubaa Nov 30 '16 at 14:10
  • See the second answer (the more upvoted one) to this question: http://stackoverflow.com/questions/23921117/disable-only-full-group-by on how to disable only_full_group_by mode @firasKoubaa – baao Nov 30 '16 at 14:13
  • i have used this to depass that problem : ANY_VALUE(`datetime`) – firasKoubaa Nov 30 '16 at 14:54
0
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;
Sohail Ahmed
  • 1,047
  • 1
  • 11
  • 27