How shall I modify my trigger so that I can get the total_employees according to their departments?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
tables are given below.
CREATE TABLE `departments` (
department_id INT(2) NOT NULL AUTO_INCREMENT,
department_name VARCHAR(30) NOT NULL,
total_employees INT(4),
PRIMARY KEY (department_id),
UNIQUE (department_name));
CREATE TABLE `employees` (
employee_id INT(4) NOT NULL AUTO_INCREMENT,
employee_email VARCHAR(30) NOT NULL,
employee_first_name VARCHAR(30) NOT NULL,
employee_last_name VARCHAR(30) NOT NULL,
department_name VARCHAR(30) NOT NULL,
PRIMARY KEY (employee_id),
UNIQUE (employee_email),
FOREIGN KEY (department_name)
REFERENCES departments (department_name)
ON DELETE CASCADE);
this is the trigger, I want it to be showing the sum of total employees in each department.
delimiter $$
create trigger department_wise_total_employee_counting
after insert on employees
for each row begin update departments set total_employees=total_employees+1
where department_id=department_id; end$$ delimiter ;
INSERT INTO `departments`
VALUES
(1,'HRM',0),(2,'Accounting',0);
INSERT INTO `employees`
VALUES
(1,'bh@gmail.com','A','B','HRM'),
(2,'ak@gmail.com','C','D','HRM'),
(3,'mr@gmail.com','E','F','HRM'),
(4,'pr@gmail.com','G','H','Accounting');
On running the following query :
select * from departments;
I'm getting this output, which just gives the total employee count rather than the total for each department.
I am trying to get total_employees=3 for HRM and total_employees=1 for Accounting.
Would appreciate any sort of suggestions.
mysql database-trigger
edited Nov 29 '18 at 5:45
Iain Duncan
1,321817
asked Nov 29 '18 at 5:36
Bappi_SBBappi_SB
407
add a comment |
0
tables are given below.
CREATE TABLE `departments` (
department_id INT(2) NOT NULL AUTO_INCREMENT,
department_name VARCHAR(30) NOT NULL,
total_employees INT(4),
PRIMARY KEY (department_id),
UNIQUE (department_name));
CREATE TABLE `employees` (
employee_id INT(4) NOT NULL AUTO_INCREMENT,
employee_email VARCHAR(30) NOT NULL,
employee_first_name VARCHAR(30) NOT NULL,
employee_last_name VARCHAR(30) NOT NULL,
department_name VARCHAR(30) NOT NULL,
PRIMARY KEY (employee_id),
UNIQUE (employee_email),
FOREIGN KEY (department_name)
REFERENCES departments (department_name)
ON DELETE CASCADE);
this is the trigger, I want it to be showing the sum of total employees in each department.
delimiter $$
create trigger department_wise_total_employee_counting
after insert on employees
for each row begin update departments set total_employees=total_employees+1
where department_id=department_id; end$$ delimiter ;
INSERT INTO `departments`
VALUES
(1,'HRM',0),(2,'Accounting',0);
INSERT INTO `employees`
VALUES
(1,'bh@gmail.com','A','B','HRM'),
(2,'ak@gmail.com','C','D','HRM'),
(3,'mr@gmail.com','E','F','HRM'),
(4,'pr@gmail.com','G','H','Accounting');
On running the following query :
select * from departments;
I'm getting this output, which just gives the total employee count rather than the total for each department.
I am trying to get total_employees=3 for HRM and total_employees=1 for Accounting.
Would appreciate any sort of suggestions.
mysql database-trigger
edited Nov 29 '18 at 5:45
Iain Duncan
1,321817
asked Nov 29 '18 at 5:36
Bappi_SBBappi_SB
407
2
use mysql view instead of trigger
– Tamil Selvan C
Nov 29 '18 at 6:00
@TamilSelvanC would be great if you could demonstrate your idea a bit.
– Bappi_SB
Nov 29 '18 at 6:07
1
You shouldn't store stuff you can easily calculate - also you would need a delete trigger if someone leaves and an update trigger if employee changes department.
– P.Salmon
Nov 29 '18 at 7:29
In an insert trigger I would expect to see reference to NEW. values.where department_id=department_id should probably be where department_id=NEW.department_id
– P.Salmon
Nov 29 '18 at 7:32
add a comment |
0
0
0
1
tables are given below.
CREATE TABLE `departments` (
department_id INT(2) NOT NULL AUTO_INCREMENT,
department_name VARCHAR(30) NOT NULL,
total_employees INT(4),
PRIMARY KEY (department_id),
UNIQUE (department_name));
CREATE TABLE `employees` (
employee_id INT(4) NOT NULL AUTO_INCREMENT,
employee_email VARCHAR(30) NOT NULL,
employee_first_name VARCHAR(30) NOT NULL,
employee_last_name VARCHAR(30) NOT NULL,
department_name VARCHAR(30) NOT NULL,
PRIMARY KEY (employee_id),
UNIQUE (employee_email),
FOREIGN KEY (department_name)
REFERENCES departments (department_name)
ON DELETE CASCADE);
this is the trigger, I want it to be showing the sum of total employees in each department.
delimiter $$
create trigger department_wise_total_employee_counting
after insert on employees
for each row begin update departments set total_employees=total_employees+1
where department_id=department_id; end$$ delimiter ;
INSERT INTO `departments`
VALUES
(1,'HRM',0),(2,'Accounting',0);
INSERT INTO `employees`
VALUES
(1,'bh@gmail.com','A','B','HRM'),
(2,'ak@gmail.com','C','D','HRM'),
(3,'mr@gmail.com','E','F','HRM'),
(4,'pr@gmail.com','G','H','Accounting');
On running the following query :
select * from departments;
I'm getting this output, which just gives the total employee count rather than the total for each department.
I am trying to get total_employees=3 for HRM and total_employees=1 for Accounting.
Would appreciate any sort of suggestions.
mysql database-trigger
edited Nov 29 '18 at 5:45
Iain Duncan
1,321817
asked Nov 29 '18 at 5:36
Bappi_SBBappi_SB
407
tables are given below.
CREATE TABLE `departments` (
department_id INT(2) NOT NULL AUTO_INCREMENT,
department_name VARCHAR(30) NOT NULL,
total_employees INT(4),
PRIMARY KEY (department_id),
UNIQUE (department_name));
CREATE TABLE `employees` (
employee_id INT(4) NOT NULL AUTO_INCREMENT,
employee_email VARCHAR(30) NOT NULL,
employee_first_name VARCHAR(30) NOT NULL,
employee_last_name VARCHAR(30) NOT NULL,
department_name VARCHAR(30) NOT NULL,
PRIMARY KEY (employee_id),
UNIQUE (employee_email),
FOREIGN KEY (department_name)
REFERENCES departments (department_name)
ON DELETE CASCADE);
this is the trigger, I want it to be showing the sum of total employees in each department.
delimiter $$
create trigger department_wise_total_employee_counting
after insert on employees
for each row begin update departments set total_employees=total_employees+1
where department_id=department_id; end$$ delimiter ;
INSERT INTO `departments`
VALUES
(1,'HRM',0),(2,'Accounting',0);
INSERT INTO `employees`
VALUES
(1,'bh@gmail.com','A','B','HRM'),
(2,'ak@gmail.com','C','D','HRM'),
(3,'mr@gmail.com','E','F','HRM'),
(4,'pr@gmail.com','G','H','Accounting');
On running the following query :
select * from departments;
I'm getting this output, which just gives the total employee count rather than the total for each department.
I am trying to get total_employees=3 for HRM and total_employees=1 for Accounting.
Would appreciate any sort of suggestions.
mysql database-trigger
mysql database-trigger
edited Nov 29 '18 at 5:45
Iain Duncan
1,321817
asked Nov 29 '18 at 5:36
Bappi_SBBappi_SB
407
edited Nov 29 '18 at 5:45
Iain Duncan
1,321817
asked Nov 29 '18 at 5:36
Bappi_SBBappi_SB
407
edited Nov 29 '18 at 5:45
Iain Duncan
1,321817
edited Nov 29 '18 at 5:45
Iain Duncan
1,321817
edited Nov 29 '18 at 5:45
Iain Duncan
1,321817
1,321817
asked Nov 29 '18 at 5:36
Bappi_SBBappi_SB
407
asked Nov 29 '18 at 5:36
Bappi_SBBappi_SB
407
asked Nov 29 '18 at 5:36
Bappi_SBBappi_SB
407
407
2
use mysql view instead of trigger
– Tamil Selvan C
Nov 29 '18 at 6:00
@TamilSelvanC would be great if you could demonstrate your idea a bit.
– Bappi_SB
Nov 29 '18 at 6:07
1
You shouldn't store stuff you can easily calculate - also you would need a delete trigger if someone leaves and an update trigger if employee changes department.
– P.Salmon
Nov 29 '18 at 7:29
In an insert trigger I would expect to see reference to NEW. values.where department_id=department_id should probably be where department_id=NEW.department_id
– P.Salmon
Nov 29 '18 at 7:32
add a comment |
2
use mysql view instead of trigger
– Tamil Selvan C
Nov 29 '18 at 6:00
@TamilSelvanC would be great if you could demonstrate your idea a bit.
– Bappi_SB
Nov 29 '18 at 6:07
1
You shouldn't store stuff you can easily calculate - also you would need a delete trigger if someone leaves and an update trigger if employee changes department.
– P.Salmon
Nov 29 '18 at 7:29
In an insert trigger I would expect to see reference to NEW. values.where department_id=department_id should probably be where department_id=NEW.department_id
– P.Salmon
Nov 29 '18 at 7:32
2
2
use mysql view instead of trigger
– Tamil Selvan C
Nov 29 '18 at 6:00
use mysql view instead of trigger
– Tamil Selvan C
Nov 29 '18 at 6:00
@TamilSelvanC would be great if you could demonstrate your idea a bit.
– Bappi_SB
Nov 29 '18 at 6:07
@TamilSelvanC would be great if you could demonstrate your idea a bit.
– Bappi_SB
Nov 29 '18 at 6:07
1
1
You shouldn't store stuff you can easily calculate - also you would need a delete trigger if someone leaves and an update trigger if employee changes department.
– P.Salmon
Nov 29 '18 at 7:29
You shouldn't store stuff you can easily calculate - also you would need a delete trigger if someone leaves and an update trigger if employee changes department.
– P.Salmon
Nov 29 '18 at 7:29
In an insert trigger I would expect to see reference to NEW. values.where department_id=department_id should probably be where department_id=NEW.department_id
– P.Salmon
Nov 29 '18 at 7:32
In an insert trigger I would expect to see reference to NEW. values.where department_id=department_id should probably be where department_id=NEW.department_id
– P.Salmon
Nov 29 '18 at 7:32
add a comment |
1 Answer
1
active
oldest
votes
2
As was pointed out by @P.Salmon, in general you shouldn't store data you can easily calculate. For this application, a VIEW (as suggested by @TamilSelvanC) is a good solution. For example:
CREATE VIEW departments_view AS
SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS total_employees
FROM departments d
LEFT JOIN employees e ON e.department_name = d.department_name
GROUP BY d.department_id;
SELECT * FROM departments_view
Output:
department_id department_name total_employees
1 HRM 3
2 Accounting 1
3 Engineering 0
Demo on dbfiddle
answered Nov 29 '18 at 7:41
NickNick
38.7k132443
Thanks a bunch, exact solution that I've been looking for. Hats off.
– Bappi_SB
Nov 29 '18 at 8:08
No worries. Glad I could help.
– Nick
Nov 29 '18 at 8:11
But it gives default value as 1, when number of employees in a department is 0. how can I get rid of this? would appreciate any sort of suggestions.
– Bappi_SB
Dec 4 '18 at 10:58
@Bappi_SB sorry about that - I hadn't considered that possibility in my demo. I've updated the query and the demo to give the correct answer when there are no employees in a department
– Nick
Dec 4 '18 at 11:27
Thanks a bunch Sir.
– Bappi_SB
Dec 4 '18 at 11:32
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53532486%2fhow-shall-i-modify-my-trigger-so-that-i-can-get-the-total-employees-according-to%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
As was pointed out by @P.Salmon, in general you shouldn't store data you can easily calculate. For this application, a VIEW (as suggested by @TamilSelvanC) is a good solution. For example:
CREATE VIEW departments_view AS
SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS total_employees
FROM departments d
LEFT JOIN employees e ON e.department_name = d.department_name
GROUP BY d.department_id;
SELECT * FROM departments_view
Output:
department_id department_name total_employees
1 HRM 3
2 Accounting 1
3 Engineering 0
Demo on dbfiddle
answered Nov 29 '18 at 7:41
NickNick
38.7k132443
Thanks a bunch, exact solution that I've been looking for. Hats off.
– Bappi_SB
Nov 29 '18 at 8:08
No worries. Glad I could help.
– Nick
Nov 29 '18 at 8:11
But it gives default value as 1, when number of employees in a department is 0. how can I get rid of this? would appreciate any sort of suggestions.
– Bappi_SB
Dec 4 '18 at 10:58
@Bappi_SB sorry about that - I hadn't considered that possibility in my demo. I've updated the query and the demo to give the correct answer when there are no employees in a department
– Nick
Dec 4 '18 at 11:27
Thanks a bunch Sir.
– Bappi_SB
Dec 4 '18 at 11:32
add a comment |
2
As was pointed out by @P.Salmon, in general you shouldn't store data you can easily calculate. For this application, a VIEW (as suggested by @TamilSelvanC) is a good solution. For example:
CREATE VIEW departments_view AS
SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS total_employees
FROM departments d
LEFT JOIN employees e ON e.department_name = d.department_name
GROUP BY d.department_id;
SELECT * FROM departments_view
Output:
department_id department_name total_employees
1 HRM 3
2 Accounting 1
3 Engineering 0
Demo on dbfiddle
answered Nov 29 '18 at 7:41
NickNick
38.7k132443
Thanks a bunch, exact solution that I've been looking for. Hats off.
– Bappi_SB
Nov 29 '18 at 8:08
No worries. Glad I could help.
– Nick
Nov 29 '18 at 8:11
But it gives default value as 1, when number of employees in a department is 0. how can I get rid of this? would appreciate any sort of suggestions.
– Bappi_SB
Dec 4 '18 at 10:58
@Bappi_SB sorry about that - I hadn't considered that possibility in my demo. I've updated the query and the demo to give the correct answer when there are no employees in a department
– Nick
Dec 4 '18 at 11:27
Thanks a bunch Sir.
– Bappi_SB
Dec 4 '18 at 11:32
add a comment |
2
2
2
As was pointed out by @P.Salmon, in general you shouldn't store data you can easily calculate. For this application, a VIEW (as suggested by @TamilSelvanC) is a good solution. For example:
CREATE VIEW departments_view AS
SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS total_employees
FROM departments d
LEFT JOIN employees e ON e.department_name = d.department_name
GROUP BY d.department_id;
SELECT * FROM departments_view
Output:
department_id department_name total_employees
1 HRM 3
2 Accounting 1
3 Engineering 0
Demo on dbfiddle
answered Nov 29 '18 at 7:41
NickNick
38.7k132443
As was pointed out by @P.Salmon, in general you shouldn't store data you can easily calculate. For this application, a VIEW (as suggested by @TamilSelvanC) is a good solution. For example:
CREATE VIEW departments_view AS
SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS total_employees
FROM departments d
LEFT JOIN employees e ON e.department_name = d.department_name
GROUP BY d.department_id;
SELECT * FROM departments_view
Output:
department_id department_name total_employees
1 HRM 3
2 Accounting 1
3 Engineering 0
Demo on dbfiddle
answered Nov 29 '18 at 7:41
NickNick
38.7k132443
edited Dec 4 '18 at 11:26
answered Nov 29 '18 at 7:41
NickNick
38.7k132443
answered Nov 29 '18 at 7:41
NickNick
38.7k132443
answered Nov 29 '18 at 7:41
NickNick
38.7k132443
38.7k132443
Thanks a bunch, exact solution that I've been looking for. Hats off.
– Bappi_SB
Nov 29 '18 at 8:08
No worries. Glad I could help.
– Nick
Nov 29 '18 at 8:11
But it gives default value as 1, when number of employees in a department is 0. how can I get rid of this? would appreciate any sort of suggestions.
– Bappi_SB
Dec 4 '18 at 10:58
@Bappi_SB sorry about that - I hadn't considered that possibility in my demo. I've updated the query and the demo to give the correct answer when there are no employees in a department
– Nick
Dec 4 '18 at 11:27
Thanks a bunch Sir.
– Bappi_SB
Dec 4 '18 at 11:32
add a comment |
Thanks a bunch, exact solution that I've been looking for. Hats off.
– Bappi_SB
Nov 29 '18 at 8:08
No worries. Glad I could help.
– Nick
Nov 29 '18 at 8:11
But it gives default value as 1, when number of employees in a department is 0. how can I get rid of this? would appreciate any sort of suggestions.
– Bappi_SB
Dec 4 '18 at 10:58
@Bappi_SB sorry about that - I hadn't considered that possibility in my demo. I've updated the query and the demo to give the correct answer when there are no employees in a department
– Nick
Dec 4 '18 at 11:27
Thanks a bunch Sir.
– Bappi_SB
Dec 4 '18 at 11:32
Thanks a bunch, exact solution that I've been looking for. Hats off.
– Bappi_SB
Nov 29 '18 at 8:08
Thanks a bunch, exact solution that I've been looking for. Hats off.
– Bappi_SB
Nov 29 '18 at 8:08
No worries. Glad I could help.
– Nick
Nov 29 '18 at 8:11
No worries. Glad I could help.
– Nick
Nov 29 '18 at 8:11
But it gives default value as 1, when number of employees in a department is 0. how can I get rid of this? would appreciate any sort of suggestions.
– Bappi_SB
Dec 4 '18 at 10:58
But it gives default value as 1, when number of employees in a department is 0. how can I get rid of this? would appreciate any sort of suggestions.
– Bappi_SB
Dec 4 '18 at 10:58
@Bappi_SB sorry about that - I hadn't considered that possibility in my demo. I've updated the query and the demo to give the correct answer when there are no employees in a department
– Nick
Dec 4 '18 at 11:27
@Bappi_SB sorry about that - I hadn't considered that possibility in my demo. I've updated the query and the demo to give the correct answer when there are no employees in a department
– Nick
Dec 4 '18 at 11:27
Thanks a bunch Sir.
– Bappi_SB
Dec 4 '18 at 11:32
Thanks a bunch Sir.
– Bappi_SB
Dec 4 '18 at 11:32
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53532486%2fhow-shall-i-modify-my-trigger-so-that-i-can-get-the-total-employees-according-to%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
use mysql view instead of trigger
– Tamil Selvan C
Nov 29 '18 at 6:00
@TamilSelvanC would be great if you could demonstrate your idea a bit.
– Bappi_SB
Nov 29 '18 at 6:07
1
You shouldn't store stuff you can easily calculate - also you would need a delete trigger if someone leaves and an update trigger if employee changes department.
– P.Salmon
Nov 29 '18 at 7:29
In an insert trigger I would expect to see reference to NEW. values.where department_id=department_id should probably be where department_id=NEW.department_id
– P.Salmon
Nov 29 '18 at 7:32