英文:
How can I get count of total order weekly between two dates in SQL?
问题
在SQL Firebird中，在两个日期之间按周获取总订单数，例如，开始日期为'2019-09-18'，结束日期为'2019-12-01'。我想要获取这两个日期之间每周的总订单数和这一周的第一天。
我尝试了以下方法，但是是错误的：
SELECT COUNT(*), dat FROM RB r
WHERE TARIH BETWEEN '2019-09-18' AND '2019-12-01'
GROUP BY DATEADD(DAY, 7, '2019-09-18') AS dat;
另外，在Firebird中无法获取周数：
SELECT DATEDIFF(ww ,date '2019-09-18' , date '2019-12-01');
> SQL错误[335544634][42000]：动态SQL错误；SQL错误代码 = -104；未知标记 - 第1行，第17列；ww [SQLState:42000，ISC错误代码:335544634]
英文:
I want to get count of total orders weekly between two dates in SQL, Firebird. For instance, let's say start date is '2019-09-18' and end date is '2019-12-01'. I want to get the total number of orders for each week between these two dates and the first day of this week.
I tried something like this but it is wrong:
SELECT COUNT(*), dat FROM RB r
WHERE TARIH BETWEEN '2019-09-18' AND '2019-12-01'
GROUP BY DATEADD(DAY, 7, '2019-09-18') AS dat;
Also, I couldn't even get weeks in Firebird:
SELECT DATEDIFF(ww ,date '2019-09-18' , date '2019-12-01');
> SQL Error [335544634] [42000]: Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 17; ww [SQLState:42000, ISC error code:335544634]
答案1
得分: 1
我已经这样解决了问题：
SELECT COUNT(*), EXTRACT(WEEK FROM r.TARIH), MIN(r.TARIH)
FROM RB r
WHERE r.TARIH BETWEEN DATE '2019-09-18' AND DATE '2019-12-01'
GROUP BY EXTRACT(WEEK FROM r.TARIH);
英文:
I have solved the problem like this:
SELECT count(*),extract (week from r.TARIH), min( r.TARIH)
FROM RB r
WHERE r.TARIH BETWEEN date '2019-09-18' AND date '2019-12-01'
GROUP BY extract (week from r.TARIH);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论