sql 일별통계, 주간통계, 월별통계

출처 : Error related to only_full_group_by when executing a query in MySql - Stack Overflow
[Mysql] DB일간 주간 월간 년간 통계 | database

 

 

sample 테이블 생성

 

CREATE TABLE `test_st` (

  `reg_date` date NOT NULL,

  `value` int(11) NOT NULL

);

 

INSERT INTO `test_st` (`reg_date`, `value`) VALUES

  ('2017-03-01', 4),

  ('2017-03-08', 2),

  ('2017-03-15', 4),

  ('2017-03-22', 4),

  ('2017-04-01', 9),

  ('2017-04-02', 1),

  ('2017-04-04', 2),

  ('2017-04-09', 4),

  ('2017-04-11', 2),

  ('2017-04-16', 4),

  ('2017-04-18', 5),

  ('2017-04-23', 3),

  ('2017-04-25', 4),

  ('2017-04-30', 4),

  ('2017-04-01', 5);

 

 

 

MySQL - 일별통계

 

SELECT DATE(`reg_date`) AS `date`,

       sum(`value`)

  FROM test_st

 GROUP BY `date`;

 

 

 

 

MySQL - 주간통계

 

SELECT DATE_FORMAT(DATE_SUB(`reg_date`, INTERVAL (DAYOFWEEK(`reg_date`)-1) DAY), '%Y/%m/%d') as start,

       DATE_FORMAT(DATE_SUB(`reg_date`, INTERVAL (DAYOFWEEK(`reg_date`)-7) DAY), '%Y/%m/%d') as end,

       DATE_FORMAT(`reg_date`, '%Y%U') AS `date`,

       sum(`value`)

  FROM test_st

 GROUP BY date;

 

 

 

MySQL - 월간통계

 

SELECT MONTH(`reg_date`) AS `date`,

       sum(`value`)

  FROM test_st

 GROUP BY `date`;

 

 

 

 

기간별 통계

 

SELECT DATE(`reg_date`) AS `date`,

       sum(`value`)

  FROM test_st

 WHERE DATE(`reg_date`) >= STR_TO_DATE('2017-04-01', '%Y-%m-%d')

   AND DATE(`reg_date`) <= STR_TO_DATE('2017-04-10', '%Y-%m-%d')

 GROUP BY `date`;