counting total records v/s no of records based on a given condition ..in a single sql query

• • Reply

  12 years ago

  by chandrubngit

  CHANDRASHEKAR B

  Bangalore, India

  Joined 12 years ago
  i'm using vb.net 2008 with msaccess 2003 database.i'm writing an windows application for calculating the employees attendance details like Number of days worked, No of days Absent.I've a column named "Status" in access table. query="SELECT Employee_ID, Employee_Name, count (*) as [Total Days] From EmpTable Where (Employee_Name='" & ComboBox1.Text & "' and Attendance_Date between #" & Me.DateTimePicker1.Value.Date & "# and #" & Me.DateTimePicker2.Value.Date & "#) group by Employee_ID, Employee_Name" this query me ...Total Days (total working days including absent & present days of employee) as shown in the picture (Total Days column).I also wanted to show a new column which gives No of Days Present...so my query should look like this.... select count(*) as [Days Present] from EmpTable Where (Employee_Name='" & ComboBox1.Text & "' and Attendance_Date between #" & Me.DateTimePicker1.Value.Date & "# and #" & Me.DateTimePicker2.Value.Date & "# and status='P') i'm adding status column condition in where clause..."P" stands for present...as stored in status column of access database. my requirement is to show the result using both the above mentioned queries...i mean how to combine these two queries into a single query which gives the output as i mentioned.
  Report abuse
• • Reply

  12 years ago

  by Slicksim

  Simon Cooper

  Lancaster, United Kingdom

  Joined 15 years ago
  Hi, I am not an SQL guru by any means, but when I have to do something like this, then I tend to use Case when in Oracle. Do you know if Access has an equivalent? You could use it in this scenario by just selecting every status of attendance from the correct date range, and then use something like: select EmployeeId, EmployeeName, case when status='P' then +1 else 0 end as "Days Present", case when status='A' then +1 else 0 end as "Days Absent", from [tablename] where employeeName= :EmployeeName and attendanceDate between :StartDate and :EndDate group by EmployeeID, EmployeeName Note the use of parameterised queries to stop people running amok and using SQL injection attacks against you. Let me know if you need any more help. Regards Simon C
  Report abuse