Click here to Skip to main content
15,037,168 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.
   
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.
[no name] 17-Sep-12 2:15am
   
ya i am sure..
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
   
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..
__TR__ 17-Sep-12 5:15am
   
Did not understand your requirement. Can you elaborate.
[no name] 17-Sep-12 5:28am
   
means i want to count the total number of time i clicked that present and absent..because i have to count the total working day and total absent in a month
__TR__ 17-Sep-12 5:31am
   
Try this
SELECT SUM(CASE WHEN present = 'True' THEN 1 ELSE 0 END) AS [Total Present Count],
SUM(CASE WHEN present = 'False' THEN 1 ELSE 0 END) AS [Total Absent Count]
from attendance
[no name] 17-Sep-12 6:00am
   
no actully i changed the datatype to int...
__TR__ 17-Sep-12 6:06am
   
Then change 'True' to the value that is currently stored (0 or 1 or whatever value you have assigned). Same thing with 'False'.
SELECT SUM(CASE WHEN present = 1 THEN 1 ELSE 0 END) AS [Total Present Count],
SUM(CASE WHEN present = 0 THEN 1 ELSE 0 END) AS [Total Absent Count]
from attendance
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
   
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 
   
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
   

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