generate report grouped by day and department mysql

Question

I have following table.

---+---------------+-------------+--------+
id | department_id | created     | amount |
---+---------------+-------------+--------+
1  |             0 | 2016-05-05  |    500 |
2  |             2 | 2016-05-05  |    100 |
3  |             1 | 2016-05-05  |    160 |
4  |             2 | 2016-05-05  |    260 |
5  |             1 | 2016-05-04  |    100 |
6  |             0 | 2016-05-04  |    150 |
7  |             1 | 2016-05-04  |    160 |
8  |             2 | 2016-05-04  |    160 |
---+---------------+-------------+--------+

I want to generate report to show how much collection is there on particular day in particular department & need to show like below table:

Date       |  0    |     1   |   2    |
2016-05-05 |  500  |  160    |  360   |
2016-05-04 |  150  |  260    |  160   |

I am using cakephp. If I use GROUP BY created it works and if I use GROUP BY department_id it works. But I need both GROUP BY to generate the required report.

I also tried generating the from to date array using this. and then tried running GROUP BY created on each date using following CakePHP query:

 //$ar = date array
    foreach ($ar as $k) {   
       $this->Transaction->find('all',array('conditions'=>array('Transaction.created'=>$k),'fields'=>array('SUM(Transaction.amount) AS s','Transaction.department_id'),'group'=>array('Transaction.department_id')));
    }

But it seems like slow solution to me if date range is long. Is there any best way to do it in single query.

Please help me sorting this problem.

@Strawberry I am unable to understand his answer. Actually I didn't find GROUP_BY in it. Can you please elaborate. — عثمان غني, Aug 30 '16 at 15:19
It didn't need a GROUP BY clause because it only contained aggregated columns. By contrast, yours does need a GROUP BY clause, but I'm sure you can figure that out. — Strawberry, Aug 30 '16 at 15:25
actually i was unable to figure. so asked the question here. so that someone help me. — عثمان غني, Aug 30 '16 at 16:28

score 0 · Answer 1 · answered Aug 30 '16 at 16:42

0

Variations of this question are asked and answered endlessly. A typical solution goes something like this...

SELECT created date
     , SUM(CASE WHEN department_id = 0 THEN amount END) dept0
     , SUM(CASE WHEN department_id = 1 THEN amount END) dept1
     , SUM(CASE WHEN department_id = 2 THEN amount END) dept2
  FROM my_table
 GROUP
    BY date;

answered Aug 30 '16 at 16:42

Strawberry

33,750
13
40
57

What if department_id column contain thousands of dept ids? you can't assume only three departments. – Kuldeep Choudhary Aug 30 '16 at 17:08
@KuldeepChoudhary You can construct a sproc to handle such a scenario - but I wouldn't. It's generally best to handle issues of data display in application level code - in this case a simple php loop, say, acting upon a well-ordered array. – Strawberry Aug 30 '16 at 18:40

score 0 · Answer 2 · answered Aug 30 '16 at 17:04

simple group by two columns department_id and created - and sum(amount) function to calculate total for each group.

$report_rows = $this->{{Model_NAME}}->find('all', array(
    'fields' => array('department_id', 'created', 'sum(amount)'),
    'group' => 'department_id, created'
));

and then you can show this report on view

$dates = Set::extract('/{{Model_NAME}}/created', $report_rows);
$departments = Set::extract('/{{Model_NAME}}/department_id', $report_rows);

Now you can make dates in columns and departments as row title. Hope this help, it's not complied, may contain some bugs but logic will work well.

according to your answer report is not looks like what i need. — عثمان غني, Aug 31 '16 at 06:59

generate report grouped by day and department mysql

2 Answers2