使用Access (SQL)查询按日期将(直方图)数据分组为多个字段

Question

我想试着整理一个回报直方图，并将其按部门分组，但举个例子，假设我们的司机很容易撞车。我想知道每个司机每个季度撞车多少次，这样我就能看出他们在整个驾驶过程中是否变得更糟了。

下面是一个示例数据表：

crash_date  driver  car
10/26/2013  Jane    Hundo
11/3/2013   Bob     Hundo
11/6/2013   John    Ferd
11/12/2013  John    Superoo
11/12/2013  Bob     Ferd
1/20/2014   John    Superoo
3/5/2014    John    Superoo
3/17/2014   Bob     Superoo
5/24/2014   Bob     Hundo

我想得到的是按司机和季度组织的撞车次数：

Quarter Bob     Jane    John
Q4Y13   2       1       2
Q1Y14   1       0       2
Q2Y14   1       0       0
Q3Y14   0       0       0

利用this question提供的信息让我了解到了这一点：

SELECT  crash_year as Year, Count(*) as Bob
FROM    (SELECT  year(crash_date) as crash_year
        FROM    (SELECT DISTINCT crash_date, driver
                FROM            crash_table
                WHERE           driver = "Bob") AS subQuery1) AS subQuery2
GROUP   BY crash_year
ORDER   BY crash_year;

这给了我这个结果：

Year    Bob
2013    2
2014    2

到目前为止，我的搜索结果并不多，但我会继续寻找。

Answer 1

这将适用于动态数量的驱动程序。

TRANSFORM Nz(Count(D.driver),0) AS CountOfdriver
SELECT "Q" & DatePart("q",[crash_date]) & "Y" & Year([crash_date]) AS Quarter
FROM Table1 D
GROUP BY "Q" & DatePart("q",[crash_date]) & "Y" & Year([crash_date])
PIVOT D.driver;

输出如下所示

Quarter Bob Jane    John
Q1Y2014 1   0       2
Q2Y2014 1   0       0
Q4Y2013 2   1       2

Answer 2

也许这能做你想做的事：

select datepart("yyyy", crash_date) as yr, datepart("q", crash_date),
       sum(iif(driver = 'Bob', 1, 0)) as Bob,
       sum(iif(driver = 'Jane', 1, 0)) as Jane,
       sum(iif(driver = 'John', 1, 0)) as John
from crash_table
group by datepart("yyyy", crash_date) , datepart("q", crash_date)
order by 1, 2;