...
Code Block | ||
---|---|---|
| ||
mysql> SELECT user.name , time.year , time.month_name , status.status_char , COUNT(*) as "job count" FROM job_fact AS fact INNER JOIN user_dim AS user ON fact.user_sk=user.user_sk INNER JOIN time_dim AS time ON fact.time_sk=time.time_sk INNER JOIN jobstatus_dim asAS status ON fact.jobstatus_sk=status.jobstatus_sk WHERE time.year = 2013 GROUP BY user.name , time.month , status.status_int ORDER BY user.name , time.month ,status.status_char ; +---------------+------+------------+-------------+-----------+ | name | year | month_name | status_char | job count | +---------------+------+------------+-------------+-----------+ | Administrator | 2013 | January | failed | 4 | | bar | 2013 | July | complete | 1 | | foo | 2013 | July | complete | 2 | | foobar | 2013 | July | complete | 1 | | foobar | 2013 | September | complete | 2 | | fubar | 2013 | July | complete | 1 | | jburk | 2013 | March | complete | 1 | | jburk | 2013 | May | complete | 100 | | jburk | 2013 | May | failed | 11 | | jburk | 2013 | May | killed | 12 | | jburk | 2013 | June | complete | 4 | | jburk | 2013 | June | failed | 20 | | jburk | 2013 | July | complete | 134 | | jburk | 2013 | July | failed | 33 | | jburk | 2013 | July | killed | 53 | | jburk | 2013 | August | complete | 67 | | jburk | 2013 | August | failed | 25 | | jburk | 2013 | August | killed | 48 | | jburk | 2013 | September | complete | 38 | | jburk | 2013 | September | failed | 17 | | jburk | 2013 | September | killed | 13 | | jburk | 2013 | October | complete | 450 | | jburk | 2013 | October | failed | 61 | | jburk | 2013 | October | killed | 49 | | jburk | 2013 | November | complete | 38 | | jburk | 2013 | November | failed | 12 | | jburk | 2013 | November | killed | 9 | | jburk | 2013 | December | complete | 3812 | | jburk | 2013 | December | failed | 627 | | jburk | 2013 | December | killed | 28 | | perforce | 2013 | January | failed | 46 | | perforce | 2013 | January | killed | 82 | | perforce | 2013 | February | complete | 3 | | perforce | 2013 | February | killed | 1 | | root | 2013 | January | complete | 24 | | root | 2013 | January | failed | 6 | | root | 2013 | January | killed | 1 | | root | 2013 | February | complete | 34 | | root | 2013 | February | killed | 3 | | root | 2013 | March | complete | 7 | | root | 2013 | April | complete | 9 | | root | 2013 | April | failed | 4 | | root | 2013 | April | killed | 4 | | root | 2013 | May | complete | 6 | | root | 2013 | May | killed | 1 | | root | 2013 | June | complete | 1 | | root | 2013 | July | complete | 7 | | root | 2013 | July | failed | 1 | | root | 2013 | July | killed | 3 | | root | 2013 | September | complete | 1 | | root | 2013 | September | failed | 1 | | root | 2013 | December | complete | 2 | | shinya | 2013 | January | complete | 4 | | shinya | 2013 | January | failed | 7 | | shinya | 2013 | January | killed | 12 | | shinya | 2013 | February | complete | 60 | | shinya | 2013 | February | failed | 2 | | shinya | 2013 | February | killed | 7 | | shinya | 2013 | March | complete | 2 | | shinya | 2013 | March | failed | 2 | | shinya | 2013 | March | killed | 3 | | shinya | 2013 | April | complete | 16 | | shinya | 2013 | April | killed | 4 | | shinya | 2013 | May | killed | 2 | +---------------+------+------------+-------------+-----------+ |
...