Quick bit of help with some SQL

[Phil99]

I have some MS-SQL I'm working on at the moment where I need to work out the % of successful appointments for reps based on a field in the Appointment table. Current SQL is:

SELECT repID, COUNT(*) AS success FROM Appointment WHERE (booked = 1) GROUP BY repID

SELECT repID, COUNT(*) AS fail FROM Appointment WHERE (booked = 0) GROUP BY repID

Now, both of these statements do exactly what I want, the problem is I want to do them both in the same statement so I can produce a ratio for each rep so I can go on and produce some charts/graphs

Desired output would be

repID = 1, conversion = 0.67 (2 success, 1 fail)
repID = 2, conversion = 0.50 (1 success, 1 fail)

and so on

Cheers for any help

 

[rpstewart]

Not sure if you can do it with SQL Server but with DB2 you can put those two queries within the FROM clause to give you something like:

select a.repid, a.fail, b.success
from (fail query) a, (success query) b
where a.repid=b.repid

 

[Phil99]

I managed to sort the original problem with case statements but I'll certainly keep in mind your suggestion for any future issues.

Thanks