problem with output of count() function ?

navedjobs · #1 (**permalink**) 12-03-11, 07:46

Code:

select count(Emp_status) from emp_att where Emp_status = 'present'  and
 date like ('%-jan-2012') and emp_id = 1
 union select count(emp_status) from emp_att where emp_status =  'absent' and emp_id = 1 and date like ('%-jan-2012' )

i m using this quary to get the no. of present & absent days of employee
but my problem is
when the output is shown , the order in which absent & present is shown is unpredictable
becuase sometimes it shows me
like this
No column name
----------------
0 //this is absent days
5 // this is present days

and sometimes i mean when i change the quary to this (only date part is changed)

Code:

select count(Emp_status) from emp_att where Emp_status = 'present'  and
 date like ('%-dec-2011') and emp_id = 1
 union select count(emp_status) from emp_att where emp_status =  'absent' and emp_id = 1 and date like ('%-dec-2011' )

then the ouput is reverse
No column name
----------------
0 // this is present days
5 // this is absent days
how to solve this problem ?

Wim · #2 (**permalink**) 12-03-11, 08:51

Include the column that holds the information that you need, and include that coulumn in the GROUP BY clause:

If you want an ordered result set, you explicitly have to tell how you want your result set ordered:

Code:

select Emp_status, count(Emp_status) AS [#]
from emp_att 
where Emp_status = 'present'  and
	date like ('%-jan-2012') and 
	emp_id = 1
GROUP BY Emp_status
	UNION 
select Emp_status, count(emp_status) 
from emp_att 
where emp_status = 'absent' and 
	emp_id = 1 and 
	date like ('%-jan-2012' )
GROUP BY Emp_status
ORDER BY Emp_status Desc

This is another way to display the information:

Code:

select SUM(CASE WHEN Emp_status = 'present' THEN 1 ELSE 0 END) As TimesPresent,
	 SUM(CASE WHEN Emp_status = 'absent' THEN 1 ELSE 0 END) As TimesAbsent
from emp_att 
where date like ('%-jan-2012') and 
	emp_id = 1

This date like ('%-jan-2012') is twice bad:
- using a reserved word ("date") for a user object (column, table, view, ...)
- storing a date in a CHAR or VARCHAR column.
If you application is not yet in production, correct those two flaws right away. It will become harder and harder to get rid of them the longer your program lives.
A good replacement could be:

Code:

	WorkDate	DATE	NOT NULL,

Rudy (r937), has already suggested this in another thread you have started, and also gave you the reason why it is better.

Internet Services

Web Development

Digital Marketing

Consumer Tech