Quick bit of help with some SQL

Soldato
Joined
29 Oct 2004
Posts
10,884
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:

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

Code:
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
 
I managed to sort the original problem with case statements but I'll certainly keep in mind your suggestion for any future issues.

Thanks :)
 
Back
Top Bottom