Data Query group, by month, day, hour

Select Year (payment_date), sum (Case month (payment_date) When 01 then amount end) 'sale in February ', sum (Case month (payment_date) When 02 then amount end) 'October sales', sum (Case month (payment_date) When 03 then amount end) 'October sales', sum (Case month (payment_date) When 04 then amount end) 'October sales', sum (Case month (payment_date) When 05 then amount end) 'October sales', sum (Case month (payment_date) When 06 then amount end) 'October sales', sum (Case month (payment_date) When 07 then amount end) 'October sales', sum (Case month (payment_date) When 08 then amount end) 'October sales', sum (Case month (payment_date) When 09 then amount end) 'October sales', sum (Case month (payment_date) when 10 then amount end) 'October sales', sum (Case month (payment_date) when 11 then amount end) 'October sales', sum (Case month (payment_date) When 12 then amount end) 'sale in February 'from payment group by year (payment_date );

View code

Keywords: date_format

Select date_format (create_time, '% Y % U') weeks, count (caseid) count from tc_case group by weeks; select date_format (create_time,' % Y % m % D ') days, count (caseid) count from tc_case group by days; select date_format (create_time, '% Y % m') months, count (caseid) count from tc_case group by months; date_format (date, format) format the date value based on the format string. The following modifier can be used in the format string: % m month name (January ...... December) % W name of the Week (Sunday ...... Saturday) % d Date of the month with an English prefix (1st, 2nd, 3rd, and so on .) % Y year, number, 4-digit www.2cto.com % Y year, number, 2-digit % A abbreviation of the week name (Sun ...... Sat) % d number of days in the month (00 ...... 31) % E number of days in the month (0 ...... 31) % m month, number (01 ...... 12) % C month, number (1 ...... 12) % B abbreviated month name (Jan ...... Dec) % J days in a year (001 ...... 366) % H hour (00 ...... 23) % K hour (0 ...... 23) % H hour (01 ...... 12) % I hour (01 ...... 12) % L hour (1 ...... 12) % I minute, number (00 ...... 59) % R time, 12 hours (HH: mm: ss [AP] m) % t time, 24 hours (HH: mm: SS) % s seconds (00 ...... 59) % s seconds (00 ...... 59) % P am or PM % W days in a week (0 = Sunday ...... 6 = Saturday) % u Week (0 ...... 52), here Sunday is the first day of the week % u Week (0 ...... 52), Monday is the first day of the week % A text "% ".

 

MySQL

 

 

-- Select count (ID) CNT, datepart (mm, time) statistics by month) [month] from [Table] Where [time] Between '2017/08 09:10:43 'and '2017/09 04:32:37' group by datepart (mm, time) -- select count (ID) CNT, datepart (DD, time) statistics by day) [Day] from [Table] Where time between '2017/08 09:10:43 'and '2017/09 04:32:37' group by datepart (DD, time) -- count select count (ID) by hour) CNT, datepart (HH, time) [hour] from [Table] Where [time] Between '2017/08 09:10:43 'and '2017/09 04:32:37' group by datepart (HH, time)

View code

Sqlserver

 

By year month

Declare @ year intset @ year = 2009 select M as [date], sum (case when datepart (month, accessdatetime) = M then accesscount else 0 end) as [count] from Counter C, (select 1 m union all select 2 Union all select 3 Union all select 4 union all select 5 Union all select 6 Union all select 7 Union all Select 8 Union all select 9 Union all select 10 union all select 11 union all select 12) aawhere @ year = year (accessdatetime) group by m

By day
Declare @ datetime set @ datetime = getdate ()

select right(100+a,2)+ ‘:00 -> ‘+right(100+b,2)+ ‘:00 ‘ as DateSpan, sum( case when datepart(hour,AccessDateTime)> =a and datepart(hour,AccessDateTime) <b then AccessCount else 0 end ) as [Count] from Counter c , (select 0 a,1 b union all select 1,2 union all select 2,3 union all select 3,4 union all select 4,5 union all select 5,6 union all select 6,7 union all select 7,8 union all select 8,9 union all select 9,10 union all select 10,11 union all select 11,12 union all select 12,13 union all select 13,14 union all select 14,15 union all select 15,16 union all select 16,17 union all select 17,18 union all select 18,19 union all select 19,20 union all select 20,21 union all select 21,22 union all select 22,23 union all select 23,24 ) aa where datediff(day,@DateTime,AccessDateTime)=0 group by right(100+a,2)+ ‘:00 -> ‘+right(100+b,2)+ ‘:00 ‘

 

Data Query group, by month, day, hour