英文:
How to get the sum of the three column in MySQL?
问题
问题是如何将单一输出值作为三个不同列值的总和，这三个列值分别是请假总数、病假总数和其他假期总数。以下是获取这个总和的单一查询示例：
SELECT
SUM(casual_leave_days) AS casual_leave,
SUM(vaction_leave_days) AS vaction_leave,
SUM(other_leave_days) AS other_leave
FROM leaveapplydata
WHERE emp_id = '".$_POST["emp_id"]."' AND
(casual_holiday_start LIKE '".$passyear."%' OR
vaction_holiday_start LIKE '".$passyear."%' OR
other_holiday_start LIKE '".$passyear."%');
这个查询将返回在指定年份（$passyear）中的员工的请假总数、病假总数和其他假期总数。请注意，你需要将 $_POST["emp_id"] 替换为你的实际变量值，以匹配特定员工的ID。
英文:
My problem is how to get the single output value as three different column value sum as total sum of the all the column
leave count of casual leave
SELECT SUM(casual_leave_days) AS casual_leave
FROM leaveapplydata
WHERE emp_id = '".$_POST["emp_id"]."' AND casual_holiday_start
LIKE '$passyear%'
leave count of vaction leave
SELECT SUM(vaction_leave_days) AS vaction_leave
FROM leaveapplydata
WHERE emp_id = '".$_POST["emp_id"]."' AND vaction_holiday_start
LIKE '$passyear%'
leave count of other leave
SELECT SUM(other_leave_days) AS other_leave
FROM leaveapplydata
WHERE emp_id = '".$_POST["emp_id"]."' AND other_holiday_start
LIKE '$passyear%'
now i want get total holiday count in pass year using single query.how do i do it?
table structure
tbl_id emp_id casual_leave_days casual_holiday_start casual_holiday_end vaction_leave_days vaction_holiday_start vaction_holiday_end other_leave_days other_holiday_start other_holiday_end leave_reason applicant_sign actor_sign supervior_sign head_sign
1 23 9 2019-12-23 2019-12-27 0 0000-00-00 0000-00-00 0 0000-00-00 0000-00-00 Private 1 1 1 1
2 23 2 2019-12-24 2019-12-26 0 0000-00-00 0000-00-00 0 0000-00-00 0000-00-00 Private 1 1 1 1
3 23 0 0000-00-00 0000-00-00 2 2019-12-28 2019-12-30 0 0000-00-00 0000-00-00 Sickness 1 1 1 1
4 23 1 2019-12-29 2019-12-30 1 2019-12-30 2019-12-31 0 0000-00-00 0000-00-00 Private 1 1 1 1
5 25 1 2019-12-30 2019-12-31 0 0000-00-00 0000-00-00 0 0000-00-00 0000-00-00 Other 1 1 1 1
6 30 1 2020-01-01 2020-01-02 0 0000-00-00 0000-00-00 0 0000-00-00 0000-00-00 Private 1 1 1 1
7 30 5 2019-11-04 2019-11-11 0 0000-00-00 0000-00-00 0 0000-00-00 0000-00-00 Private 1 1 1 1
8 23 1 2020-01-02 2020-01-03 0 0000-00-00 0000-00-00 0 0000-00-00 0000-00-00 Sickness 1 1 1 1
答案1
得分: 1
Summing is commutative, so you could add the three fields and then sum them:
SELECT SUM(casual_leave_days + vacation_leave_days + other_leave_days) AS total_leave
FROM leaveapplydata
-- WHERE some condition
英文:
Summing is commutative, so you could add the three fields and then sum them:
SELECT SUM(casual_leave_days + vacation_leave_days + other_leave_days) AS total_leave
FROM leaveapplydata
-- WHERE some condition
答案2
得分: 0
你可以这样做，
SELECT (casual_leave + vaction_leave + other_leave_days) AS total_leave
FROM (
SELECT SUM(casual_leave_days) AS casual_leave,
SUM(vaction_leave_days) AS vaction_leave,
SUM(other_leave_days) AS other_leave
FROM leaveapplydata
WHERE emp_id = '$_POST["emp_id"]' AND (casual_holiday_start LIKE '$passyear%'
OR vaction_holiday_start LIKE '$passyear%'
OR other_holiday_start ))
英文:
You can do it like this ,
SELECT (casual_leave + vaction_leave + other_leave_days ) AS total_leave
FROM (
SELECT SUM(casual_leave_days) AS casual_leave ,
SUM(vaction_leave_days) AS vaction_leave ,
SUM(other_leave_days) AS other_leave
FROM leaveapplydata
WHERE emp_id = '".$_POST["emp_id"]."' AND (casual_holiday_start LIKE '$passyear%'
OR vaction_holiday_start LIKE '$passyear%'
OR other_holiday_start ) )
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论