Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    47

    Unanswered: problem with output of count() function ?

    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 ?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •