
July 21st, 2004, 12:57 PM
|
|
Registered User
|
|
Join Date: Jun 2004
Posts: 4
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
SQL Sub query
I would like to have these two queries in a sub query that gives me the result of query 2 divided by the result of query 1 into a percentage. Basically its a query on the number of tickets total divided by the number of tickets closed. Problem is when I run a sub query, since I am using the count function it causes some errors, if I include the "hst_name" field in the 2nd query, it gives the same result as the first one. If anyone can get something like this to work, or know a way with ASP to just calculate the two results outside of SQL, that would work too.
SELECT distinct count(hlp_id) AS Count, hst_name, b.emp_name AS rep_name
FROM dbo.helpdesk_requests LEFT JOIN dbo.helpdesk_status ON hlp_hst_id = hst_id LEFT JOIN dbo.employees b ON b.emp_id = hlp_representative_emp_id
WHERE hst_name = 'Closed'
GROUP BY hst_name, b.emp_name
ORDER BY b.emp_name
SELECT distinct (select count(hlp_id)), b.emp_name AS rep_name
FROM dbo.helpdesk_requests LEFT JOIN dbo.helpdesk_status ON hlp_hst_id = hst_id LEFT JOIN dbo.employees b ON b.emp_id = hlp_representative_emp_id
GROUP BY b.emp_name
ORDER BY b.emp_name
Thanks,
Dominic
|