Geek asks and answers

How to sum records of one column with different capabilities?

I have a mysql query:

SELECT count(*) as `present_days` 
FROM  tbl_intime_status 
WHERE employee_status = 'Out' and 
      present_status = 'Full Day' and 
      date LIKE '%/"+month2+"/"+year1+"' and 
      employee_id="+ EmpId+

I do not receive from this request. from Full Day.

There are present_status= 'Half Day'and in my database entries present_status = 'Full Day'.

How to count "Full day" + "Half day"?

+5
source share
7 answers

If you want the calculations to be split, you can do it

SELECT present_status, count(*) as `present_days` 
FROM  tbl_intime_status 
WHERE employee_status = 'Out' and 
      present_status IN ('Full Day','Half Day')
      date LIKE '%/"+month2+"/"+year1+"' and 
      employee_id="+ EmpId+
GROUP BY present_status

If you want the total number of both to do this

SELECT count(*) as `present_days` 
FROM  tbl_intime_status 
WHERE employee_status = 'Out' and 
      present_status IN ('Full Day','Half Day')
      date LIKE '%/"+month2+"/"+year1+"' and 
      employee_id="+ EmpId+
+4
source

try, do not forget to leave the column date, as this is a reserved word.

SELECT present_status, count(*) as `present_days` 
FROM   tbl_intime_status 
WHERE  employee_status = 'Out' and 
       present_status IN ('Full Day','Half Day')
       `date` LIKE '%/"+month2+"/"+year1+"' and 
       employee_id = " + EmpId + "
GROUP BY present_status
+3
source

, , :

...

present_status in ('Full Day',  'Half Day' )

...

: SQL IN.

+2

where, IN:

...
WHERE present_status IN ('Full Day','Half Day')
...

OR:

...
WHERE (present_status = 'Full Day' OR present_status = 'Half Day')
...
+1
source

This is what you are looking for:

SELECT count(*) as present_days 
FROM tbl_intime_status 
WHERE employee_status = 'Out' 
and present_status IN ('Full Day', 'Half Day') 
and date LIKE '%/"+month2+"/"+year1+"' 
and employee_id="+ EmpId
+1
source

This will let you split all types of samples in one line:

SELECT SUM(IF(present_status  = 'Full Day', 1, 0)) AS full_present_days,
       SUM(IF(present_status  = 'Half Day', 1, 0)) AS half_present_days,
       COUNT(*) AS present_days
FROM  tbl_intime_status
WHERE employee_status = 'Out' and
      present_status IN('Full Day', 'Half Day') and
      date LIKE '%/"+month2+"/"+year1+"' and
      employee_id="+ EmpId+;
0
source 
SELECT SUM(
   CASE present_status
     WHEN 'Full Day' THEN 1
     WHEN 'Half Day' THEN 0.5
     END
 ) as `present_days`  
FROM  tbl_intime_status  WHERE employee_status = 'Out'

Hope this is the query you are looking for.

0
source

More articles:


All Articles