MySQL Question

Count specific fields with inner join

I have a following schema:


create table myapp_task
(
title varchar(100) not null,
state varchar(11) not null,
estimate date not null,
my_id int not null auto_increment
primary key,
road_map_id int not null,
create_date date not null,
constraint myapp_task_road_map_id_5e114978_fk_myapp_roadmap_rd_id
foreign key (road_map_id) references myapp_roadmap (rd_id)
)
;
— auto-generated definition
create table myapp_roadmap
(
rd_id int not null auto_increment
primary key,
name varchar(50) not null
)
;


I want get number, begin and end of a week of create_date, number of all tasks and number of ready tasks (state = 'ready/in_progress')
Here is my query:

select DISTINCT week(create_date, 1) as week,
SUBDATE(create_date, WEEKDAY(create_date)) as beginofweek,
DATE(create_date + INTERVAL (6 - WEEKDAY(create_date)) DAY) as endofweek,
SUM(state) as number,
SUM(state = 'ready') as ready
from myapp_task inner join myapp_roadmap
on myapp_task.road_map_id = myapp_roadmap.rd_id;


Actually, i have a problem only with count of ready tasks.

Answer

I think you are close:

select week(create_date, 1) as week,
    SUBDATE(create_date, WEEKDAY(create_date)) as beginofweek,
    DATE(create_date + INTERVAL (6 - WEEKDAY(create_date)) DAY) as endofweek,
    count(state) as number,
    SUM(CASE WHEN state = 'ready' THEN 1 ELSE 0 END) as ready,
    SUM(CASE WHEN state = 'in_progress' THEN 1 ELSE 0 END) as in_progress
FROM myapp_task inner join myapp_roadmap
    on myapp_task.road_map_id = myapp_roadmap.rd_id
GROUP BY week, beginofweek, endofweek

Using a CASE statement you can add up just states that are ready or in_progress separately. Furthemore, the addition of a GROUP BY insures that the count is for the week. I think MySQL would probably spit out the right result without the GROUP BY in this case, but why let it guess at what you want here. Also, if you upgrade to MySQL 5.7+ then a query like this written without a GROUP BY will error by default.


Also got rid of that DISTINCT modifier. Thanks @AaronDietz