I have a relatively complex query I'm trying to design, and I'm having
getting the basic structure to work. Here is a simplified example of what
I'm trying to do:
I have one table with the following fields:
A sample of the table would be
I'm trying to design a query that would produce the following from the above
employeeID timeoffreason timeoffreason_total timeoffreasonPercent
------------ -------------- ------------------- ---------------------
1 sick 2
1 vacation 1 25.0
1 holiday 1
2 sick 1
2 vacation 1 50.0
3 sick 2
Where employeeid would be the employee, timeoffreason would be the reason,
timeoffreason_total is the distinct total for the reason of the particular
employee (so it is grouped by the employeeID), and tiemoffreasonPercent is
the percentage of a reason of the total number of reasons for a particular
The timeoffreasonPercent is what is giving me the trouble.
I can't get the total amount of records for an employee. count(employeeID)
gives me a total for a particular reason So count(employeeID) and
give me the same number, for instance in employeeID for timeoffreason: sick,
I get 2 and 2.
which would give me 100% for the timeoffreason percent if i was to divide
the timeoffreason_total by the total records for the employeeID.
Can this be done within 1 query? I could run separate queries to get the
totals per employee, but in reality this is a table containing 20+
millionrecords which also does a join on another table. But if anyone has
some suggestions for the simplified example given above, I should be able to
apply that to a larger scale query.
Thanks in advance,