Click here to Skip to main content
15,898,374 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all

i am counting the number of present in my table using the below query
SQL
select count(present)from attendance
where present='true'

but i want it employee wise counting..
please tell me how to do that..
means it should display

Emp_Name Present Count
a 2
b 3
c 0
like this..
Posted

You would need Group By clause.

Try:
SQL
SELECT 
  Emp_Name,
  COUNT(Present)
FROM
  ATTENDANCE
WHERE
  Present = 'true'
GROUP BY
  Emp_Name


BTW, a suggestion: you should make 'present' column as a BIT field instead of a string.
 
Share this answer
 
Comments
[no name] 17-Sep-12 1:55am    
i made it bit in sql but it's still saving true & false...Is there anything else i have to do to save 0 and 1
Sandeep Mewara 17-Sep-12 2:03am    
It's OK. That will save as true/false to show it a BIT field. But during comparions you can use either.

WHERE present = true (without quotes)
WHERE present = 1

Try!
__TR__ 17-Sep-12 2:06am    
+5
[no name] 17-Sep-12 2:10am    
when i using present=true(without quotes) it's showing invalid column name
Sandeep Mewara 17-Sep-12 2:12am    
You sure that you saved your table column to 'bit' datatype and then saved it?
Also, make sure all the values in it are of bit type.
Try this
SQL
SELECT Emp_Name, 
SUM(CASE WHEN present = 'True' THEN 1 ELSE 0 END) AS [Present Count],
SUM(CASE WHEN present = 'False' THEN 1 ELSE 0 END) AS [Absent Count]
from attendance
GROUP BY Emp_Name
 
Share this answer
 
v2
Comments
[no name] 17-Sep-12 1:57am    
How to pass multiple argument in this count statement..like i f i want to display the absent count also..
__TR__ 17-Sep-12 2:01am    
Check the updated solution.
[no name] 17-Sep-12 2:03am    
Got it..thanks _TR_
__TR__ 17-Sep-12 2:05am    
You are welcome.
[no name] 17-Sep-12 4:07am    
@_TR_:need one more help from u..the query u gave it's working fine but if i have to display number of total present and absent then what change i have to do..
Try this:
SQL
SELECT 
	Emp_Name AS Name
	,COUNT(present) AS Cnt
FROM tableName 
WHERE
     present = 'true'
GROUP BY 
	Emp_Name
ORDER BY 
	Name;


Or
SQL
SELECT 
       Emp_Name, 
       count(present)
FROM tableName
WHERE
     present = 'true'
GROUP BY
       Emp_Name
 
Share this answer
 
v2
Comments
__TR__ 17-Sep-12 2:06am    
+5
try this:
SQL
select Employee_name, count(present) as total_employee from attendance
where present='true'
Group By Employee_name 
 
Share this answer
 
v2
SQL
select A.Emp_Name,
  case when  Count(A.Present) in (select Count(Present)   from attendence 
                  where Present in ('false')
                  group by Emp_Name) then 0 
  else (select Count(B.Present) from attendence B
                  where A.Emp_Name = B.Emp_Name and 
                  B.Present in ('true')
                  group by Emp_Name) 
  End  Present 
  from attendence A
  where Present in ('true','false')
  group by Emp_Name
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900