SQL group by day, with count
I like this approach in (MS SQL):
SELECT
Convert(char(8), LogDate, 112),
count(distinct RefundId)
FROM RefundProcessing
GROUP BY Convert(char(8), LogDate, 112)
Top 3 services used for each day - SQL
You can use window functions with aggregation. Something like this:
SELECT b.*
FROM (SELECT date, products_name AS product,
COUNT(*) AS NumberOfBookings,
ROW_NUMBER() OVER (PARTITION BY date ORDER BY COUNT(*) DESC) as seqnum
FROM bookings
WHERE date >= '2021-06-01' AND date < '2021-07-01'
GROUP BY products_name, date
) b
WHERE seqnum <= 3
ORDER BY Date, seqnum;
Note: This uses standard date formats for the date constants.
Group by day and still show days without rows?
In order to accomplish this, you need to have a table (or derived table) which contains the dates that you can then join from, using a LEFT JOIN.
SQL operates on the concept of mathematical sets, and if you don't have a set of data, there is nothing to SELECT.
If you want more details, please comment accordingly.
SQL query for calculate cont of orders for each day
I think you want:
SELECT DATE(time_order), COUNT(*)
FROM orders
WHERE DATE(time_order) >= DATE(NOW()) - INTERVAL 7 DAY
GROUP BY DATE(time_order);
That is, your version is outputting the date for each time_order
. However, the GROUP BY
includes the time, so you are getting a row for each date/time.
MySQL Query GROUP BY day / month / year
GROUP BY YEAR(record_date), MONTH(record_date)
Check out the date and time functions in MySQL.
Related Topics
Modify Materialized View Query
How to Call a Stored Procedure from Another Stored Procedure
How Does One Escape an Apostrophe in Db2 SQL
Using Different Order by with Union
Oracle Dynamic Desc and Asc in Order By
Creating Sumif Function in SQL Server 2012
T-SQL Select Get All Months Within a Range of Years
Split Comma Separated String Table Row into Separate Rows Using Tsql
Postgres Drop Table Syntax Error
Left Inner Join VS. Left Outer Join - Why Does the Outer Take Longer
How to Find the User That Has Both a Cat and a Dog
Can SQL Profiler Display Return Result Sets Alongside the Query
What Is the Equivalent of Regexp_Substr in MySQL
Order by Maximum Condition Match
Retrieving a Row, with Data from Key-Value Pair Table in MySQL