SQL Query to Group by Day

MySQL Query GROUP BY day / month / year

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.

sql query group by day of month

I think you can use extract with count functions:

SELECT EXTRACT(day FROM CreateDate) "Day",
      COUNT(CreateDate) "Number of Reports"
      FROM yourTableName
      GROUP BY EXTRACT(day FROM CreateDate)
      ORDER BY "Number of Reports" ASC;

group by day with missing days

Try this:

declare @startDate date = '2017-10-01'
declare @endDate date = '2017-10-31'

;with cte as (
select cast(@startDate as date) [dayOfYear]
union all
select DATEADD(day, 1, [dayOfYear]) from cte
where [dayOfYear] < @endDate
)

select dayOfYear, SUM(case when Created is null then 0 else 1 end) from cte
left join MY_TABLE [T] on cte.dayOfYear = CAST(T.Created as date)
group by dayOfYear

The logic is as follows:

get table with all days between @startDate and @endDate (the CTE - I specified first and last of October). Then we left join your table and when the days has no match, we define corresponding value to 0, 1 otherwise. Then it's enough to sum these values day-wise.

SQL Group by Day of Week

You want aggregation:

SELECT SUM(CASE WHEN DATENAME(dw, MachineMidLineDate) = 'Sunday' THEN [Total Mins] END) AS Sun,
       SUM(CASE WHEN DATENAME(dw, MachineMidLineDate) = 'Monday' THEN [Total Mins] END) AS Mon,
      . . . 
FROM dbo.vw_Machine_Minutes_Overview;

Group by days interval SQL

You can use Scalar Correlated Subqueries, but don't expect good performance:

select date
  ,(select sum(reports) from REPORTS as r2 where r2.date between r.date - 7 and r.date) as `Reports 7 prev`
  ,(select sum(reports) from REPORTS as r2 where r2.date between r.date and r.date + 7) as `Reports 7 after`
from REPORTS as r
group by date

See fiddle

Select count group by day of the dateTime

You can try to use DATE_FORMAT function.

select DATE_FORMAT(date,'%Y-%m-%d'),count(*) 
from monitor 
WHERE date between DATE_SUB(NOW(), - INTERVAL 5 DAY) AND NOW()
group by DATE_FORMAT(date,'%Y-%m-%d');

sql query with avg price per day and group by day

SELECT "Name", date_trunc('day', "Time"), avg("LowPrice"), avg("HighPrice")
  FROM rp_prices
 WHERE "Time" > now() - interval '10 day'
 GROUP BY "Name", date_trunc('day', "Time")

Druid Group by Day of Week and Hour of Day

Wouldn't TIME_EXTRACT(<timestamp_expr>, HOUR) do that?

For day names, I believe you can use:

TIME_FORMAT(<timestamp_expr>, [<pattern>, [<timezone>]])

For pattern it seems you can use 'EEEE' (for full text form of day name).

---

Related Topics