I have a table named region having two columns : having region_id is primary key
|region_id | region_code |
| ----------+-------------+
| 1 | Asia |
| 2 | Can |
| 3 | Cen |
| 4 | West |
| 5 | GNW |
----------+-------------+
And i have another table employee , in which emp_id is primary key and region_id_fk is foreign key mapped to region table:
+------+---------+---------+------------+---------------+---------+------------
|emp_id| emp_name|global_id|region_id_fk|attendance_date|ispresent| is_billable|
+------+---------+---------+------------+---------------+---------+---------
| 1 | andrew | candrew | 1 | 2017-02-13 | 1 | 1 |
| 2 | andrew | candrew | 1 | 2017-02-14 | 1 | 1 |
| 3 | andrew | candrew | 1 | 2017-02-15 | 1 | 1 |
| 4 | andrew | candrew | 1 | 2017-02-16 | 1 | 1 |
| 5 | andrew | candrew | 1 | 2017-02-17 | 0 | 1 |
| 6 | simon | csimon | 1 | 2017-02-13 | 1 | 1 |
| 7 | simon | csimon | 1 | 2017-02-14 | 1 | 1 |
| 8 | simon | csimon | 1 | 2017-02-15 | 1 | 1 |
| 9 | simon | csimon | 1 | 2017-02-16 | 1 | 1 |
| 10 | simon | csimon | 1 | 2017-02-17 | 1 | 1 |
| 11 | peter | cpeter | 2 | 2017-02-13 | 1 | 1 |
| 12 | peter | cpeter | 2 | 2017-02-14 | 1 | 1 |
| 13 | peter | cpeter | 2 | 2017-02-15 | 1 | 1 |
| 14 | peter | cpeter | 2 | 2017-02-16 | 1 | 1 |
| 15 | alvin | calvin | 2 | 2017-03-13 | 1 | 0 |
| 16 | thomas | thomas | 2 | 2017-03-14 | 0 | 1 |
| 17 | samuel | csamuel | 2 | 2017-03-15 | 1 | 0 |
| 18 | jackson | cjackson| 2 | 2017-03-16 | 1 | 0 |
| 19 | clinda | clinda | 2 | 2017-03-17 | 1 | 1 |
+--------+----------+-----------+--------------+-----------------+------
I already have this type of query where I used inner join and count in a stored procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS test.some_proc $$
CREATE PROCEDURE test.some_proc(IN in_is_billable INT,IN in_month INT,IN in_ispresent INT)
BEGIN
DECLARE capacitycount INT;
DECLARE hrs INT;
SET hrs=8;
SELECT COUNT(ispresent)*8 AS team_capacity, region_code
FROM employee emp
INNER JOIN region r ON r.region_id=emp.region_id_fk
WHERE (is_billable=in_is_billable) AND
(MONTH(attendance_date)=in_month) AND
(ispresent=in_ispresent);
END $$
DELIMITER ;
And its giving me below result: team capacity|region_code 128 |Asia
I want my stored procedure to give all the region code present in region table along with team capacity which i m calculating in stored procedure which is giving me correct answer.
The result should be like this but for now I am only getting team capacity for only one region.
team capacity|region_code
128 |Asia
39 |Can
68 |Cen
Any help will be appreciated, Thanks in advance :)
you missed group by in your syntax
DELIMITER $$
DROP PROCEDURE IF EXISTS test.some_proc $$
CREATE PROCEDURE test.some_proc(IN in_is_billable INT,IN in_month INT,IN in_ispresent INT)
BEGIN
DECLARE capacitycount INT;
DECLARE hrs INT;
SET hrs=8;
SELECT COUNT(ispresent)*8 AS team_capacity, region_code
FROM employee emp
INNER JOIN region r ON r.region_id=emp.region_id_fk
WHERE (is_billable=in_is_billable) AND (MONTH(attendance_date)=in_month) AND
(ispresent=in_ispresent)
group by r.region_code;
END $$
DELIMITER ;