CODEWITHSUNDEEP
phpmysqlcodeigniter
Mirza Chilman
Mirza Chilman

Reputation: 429

Count How many Data in a month based on Criteria

I have 2 tables, master_kategori_project which is contain category of projects and project which is contains how many project in a specific month in a specific year. below is my master_kategori_project table.

id| category_project | description |
1 | Category 1       | descrip 1   |
2 | Category 2       | descrip 2   |
3 | Category 3       | descrip 3   |

and my project table look something like this

id| id_category_project | name_project | start_date |
1 | 1                   | Project 1    | 27-01-2017 |
2 | 2                   | Project 2    | 29-02-2017 |

i want to make a table that contain chart like this, u can see from the table on january i have 3 projects of kategori 1, 5 projects of kategori 2, 7 projects of kategori 3 and so on

how can i write an query that have something like this? the chart is automatically build by the table itself.

enter image description here

this is my model and i believe i did it wrong, because i can't put the data into the correct month

public function get_kategori_totals($mnth, $year=null)
{   
    $yr=date('Y');
    $select = "
        SELECT COUNT(category_project),start_date,category_project
        FROM
            project
        LEFT JOIN
            master_kategori_project
        ON
            project.id_category_project=master_kategori_project.id
        WHERE 
            MONTH(start_date)='$mnth'".
            (($year == null)?" AND YEAR(start_date)='$yr' GROUP BY category_project":"AND YEAR(stat_date)='$year' GROUP BY category_project");
    return $this->db->query($select);
}

Upvotes: 1

Views: 96

Answers (1)

Bhushan
Bhushan

Reputation: 595

Use below query.

  SELECT id_category_project, month(start_date), count(distinct name_project) as project_count
  FROM project
  WHERE start_date IS NOT NULL AND start_date <> '0000-00-00'
  GROUP BY 1,2
  ORDER BY 2,1

Upvotes: 1

Related Questions