如何对此查询记录进行排序？ [英] How to sort this query records?

问题描述

我编写此查询以获取给定日期内每月的总记录数。

查询是： -

I write this query to get total records per months within given date.
the query is:-

SELECT count(*) AS CountOfTotal, (year(invDate) & '/' & month(invDate)) AS Months
FROM SalesDetails
WHERE invDate between #07/25/15# and #04/25/16#
GROUP BY (year(invDate) & '/' & month(invDate))
ORDER BY (year(invDate) & '/' & month(invDate));

结果如下：

262 --- 2015/10

268 --- 2015/11

330 --- 2015/12

289 --- 2015/7

406 --- 2015 / 8

154 --- 2015/9



这将返回没有排序的结果，因为该值将转换为文本。



所以我如何得到月份明智的总计数和明年的分类 ??



我尝试过：



首先我试过：

results are:
262 --- 2015/10
268 --- 2015/11
330 --- 2015/12
289 --- 2015/7
406 --- 2015/8
154 --- 2015/9

this will return results without sorting, because the value is converted to text.

so how I get the month wise Total-Count and sorting on year and month wise??

What I have tried:

First I tried:

SELECT count(total) AS CountOfTotal, month(invDate) AS Months
FROM SalesDetails
WHERE invDate between #07/25/15# and #04/25/16#
GROUP BY  month(invDate)
ORDER BY  month(invDate);

在这个查询中，我无法弄清楚哪一个月...



之后我在 SQLite中尝试了它工作正常：

In this query I couldn't figure out which month in which year...

after that I tried this in SQLite it worked fine:

select sum(total) as CountOfTotal,strftime('%m',invDate) as Months from SalesDetails where invDate between '2015/05/25' and '2016/04/25' group by strftime('%m',invDate) order by invDate

由于聚合函数（Group by），这在MS Access中不起作用，我知道Sqlite查询不正确（但我不知道它是如何工作的.. ..

this not working in MS Access because of aggregate function(Group by), I know the Sqlite query is not correct(but I don't know how its working)..

推荐答案

如果列invDate的类型为Date，你可以尝试更改你的查询：

If the column invDate is of type Date you could try to change your query like this:

SELECT count(total) AS CountOfTotal, month(invDate) AS Months
FROM SalesDetails
WHERE invDate between #07/25/15# and #04/25/16#
GROUP BY  month(invDate)
ORDER BY  invDate;

你想按年和月排序，钻井平台ht？

You want to sort by both year and month, right?

我用这个查询解决了我的问题： -

I solved My problem with this Query:-

SELECT (MonthName(Month(invDate),true) &'-'&  year(invDate)) AS SalesDate, count(*) AS Total
FROM salesDetails
WHERE invDate between #07/01/2015# and #05/03/2016#
GROUP BY year(invDate), Month(invDate)
ORDER BY year(invDate), Month(invDate);

我还添加 MonthName 获取月份的函数命名并连接年份和月份，这一切都符合我的要求......

I also add MonthName function for getting the month name and concatenate the Year and Month, this all for my requirement...

这篇关于如何对此查询记录进行排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！