COUNT Two columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

COUNT Two columns

Author

Topic

vijaykumar
Starting Member

1 Post

Posted - 2004-11-23 : 11:51:07

Hi,
I have 2 tables one Employee Other Vacation Design is as follows
tbl Emp
EmpID, FirstName, LastName
1 FN LN

tbl Vacation
EmpID, VacationDate, VacationTypeID
1 11/01/2004 1
1 11/02/2004 1
1 11/10/2004 5
1 11/11/2004 5
Note: VacationTypeID 1 = FullDay 5 = HalfDay 2HalfDays = 1 FullDay

How can I have output showing EmpID, FirstName, LastName and Count VacationDate based on Both VacationTypeID
Result
EmpID FirstName LastName, FullDays, HalfDays TotalDays
1 FN LN 2 2 3

Thanks for your time and Energy
Vijay

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-03 : 00:40:06

Hi, Try this




SELECT EmpId,FirstName,LastName,(Select Count(vacationTypeId) from Vacation where VacationTypeId=1 Group by VacationTypeId) as 'Full days',
(Select Count(vacationTypeId) from Vacation where VacationTypeId=5 Group by VacationTypeId)  as 'Half Days' ,
(Select Count(vacationTypeId) from Vacation where VacationTypeId=1 ) + (Select Count(vacationTypeId) /  2  from Vacation where VacationTypeId=5 ) as 'Total Days'
from Emps E

Madhivanan

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-03 : 00:46:36

select e.EmpID, FirstName = max(e.FirstName), LastName = max(e.LastName) ,
FullDays = sum(case when v.VacationTypeID = 1 then 1 else 0 end) ,
HalfDays = sum(case when v.VacationTypeID = 2 then 1 else 0 end) ,
ToalDays = sum(case when v.VacationTypeID = 1 then 1.0 else 0.5 end)
from Emp e
join Vacation v
on e.EmpID = v.EmpID
group by e.EmpID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-03 : 06:53:00

Nigel's solution will be the most efficient, though if this is Access, use IIF() instead of CASE.

Also, do it in 2 queries -- one to summarize the data by employee first (no join to the Emp table yet, just group by EmpID), and then join those summarized results to the Employe table for your final result.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources