Fix SQL statement

Given the tables in MYsql 5 (see attachment) we need a query that can Count how many issues are in the latest state of each issue type per week The results shoud be displayed like this: +--------------------+------+----------+ | new_status | week | count(*) | +--------------------+------+----------+ | Closed | 12 | 2 | | In Progress | 12 | 3 | | In Progress | 13 | 1 | | In Progress | 16 | 2 | | Closed | 16 | 7 | | Backlog | 17 | 31 | | Aprobacion Cliente | 17 | 32 | | Desarrollo | 17 | 32 | | Control de Calidad | 17 | 4 | | New | 17 | 6 | +--------------------+------+----------+ 10 rows in set (0.00 sec) The query that did this is: mysql> select [login to view URL] as new_status , WEEKOFYEAR(j.created_on) as week, count(*) from issues left join journals as j on issues.id=j.journalized_id left join journal_details as jd on [login to view URL] = jd.journal_id left join issue_statuses as is1 on [login to view URL] = [login to view URL] where journalized_type = 'issue' and prop_key = 'status_id' and DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 6 WEEK) <= j.created_on and issues.project_id = 19 group by [login to view URL], week order by j.created_on; The problem with this query is that for week 17 there should be a grand total of 45 issues and it is displaying 105 (31+32+32+6+4). The problem could be in the join between journals and journal_details. Deliverable: Correct SQL query