Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to create a select statement that can return a table of 1 column. Basically, I am looping through all the records of NewHire, and then using its id, to select stuff from another table. I then do a count to get the number of rows, and then divide to get a percentage value, and then I want to insert it to this temp table t. Then select t. But this isn't working. I am getting syntax errors.

Does anyone know how to fix this?

declare @t table (Percentage int)
DECLARE @acc INT 
SET @acc = 1
DECLARE @max INT 
select @max = max(HireID) from NewHire
WHILE (@acc <= @max)
BEGIN
  IF (@acc in (select HireID from NewHire))
  BEGIN
    insert into @t  
      (select COUNT(*) from Hire_Response WHERE HireID = @acc) /
      (select COUNT(*) from Hire_Response WHERE HireID = @acc 
       AND (HireResponse = 0 OR HireResponse = 1))
  END
  set @acc = @acc + 1
END
select * from @t
share|improve this question
1  
And the errors are? –  dezso May 14 '13 at 15:22

2 Answers 2

up vote 2 down vote accepted

You need an additional select just after your insert.

declare @t table (Percentage int)
DECLARE @acc INT 
SET @acc = 1
DECLARE @max INT 
select @max = max(HireID) from NewHire
WHILE (@acc <= @max)
    BEGIN
        IF (@acc in (select HireID from NewHire))
            BEGIN try
                insert into @t  
                select
                    (select COUNT(*) from Hire_Response WHERE HireID = @acc) /
                    (select COUNT(*) from Hire_Response WHERE HireID = @acc AND (HireResponse = 0 OR HireResponse = 1))
            END try
            begin catch
                insert into @t 
                select 0
            end catch
        set @acc = @acc + 1
    END
select * from @t

There is more than one way to do this though, but the above is your quickest fix to the provided query

share|improve this answer
    
That worked, but now for a lot of them I get divide by zero errors, is there a way so that, you can do a check if its a 0/0, and if so, just force the result to be a 0? –  omega May 14 '13 at 14:13
    
You can use a TRY CATCH. I have updated the example above –  RoKa May 14 '13 at 14:15

Looks like this can be done with a single SQL statement, actually, something like this:

select case when n2 = 0 then 0 else n1/n2 from (
 select 
  h.hireId, 
  count(*) as n1, 
  sum(case when r.hireResponse in (0,1) then 1 else 0 end) as n2
  from
   NewHire h, Hire_Response r
  where
   h.hireId = r.hireId
  group by
   h.hireId
)

I don't have a SQL Server instance handy to test it, but it's basic SQL so I expect it to be supported.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.