1

I'm looking to try and put together a histogram for returns and group it by division, but just for an example, let's say we have drivers that are prone to crashing cars. I want to know how many times each driver has crashed a car in each quarter so I can see if they're getting better worse at the whole driving thing.

Here's a table of example data:

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

What I'd like to get is the number of crashes organized by driver and quarter:

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

Using the info from this question got me this far:

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;

Which gives me this result:

Year    Bob
2013    2
2014    2

I haven't had much more luck with my searches up to this point, but I'll keep poking around.

Community
  • 1
  • 1
Ryan B
  • 527
  • 1
  • 6
  • 17

2 Answers2

1

This will work with a dynamic number of drivers

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;

output looks like this

Quarter Bob Jane    John
Q1Y2014 1   0       2
Q2Y2014 1   0       0
Q4Y2013 2   1       2
Brad
  • 11,934
  • 4
  • 45
  • 73
0

Perhaps this does what you want:

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786