By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,742 Members | 1,570 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,742 IT Pros & Developers. It's quick & easy.

Dodgy SQL Query

P: n/a
Hi

I have a SQL query that counts values in a table and totals them up and
works out a percentage, it involves a few counts, is there a better why
to acheive what I want other than the way I have done it?

The query is:

SELECT q.`text` AS 'Question',
(SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer = 'y')
AS 'Yes',
(SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer = 'n')
AS 'No',
(SELECT COUNT(*) FROM answer WHERE question_id = q.id) AS 'Total',
((SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer =
'y') /
((SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer =
'y') + (SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer
= 'n')) * 100) AS 'Percentage Yes',
((SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer =
'n') /
((SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer =
'y') + (SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer
= 'n')) * 100) AS 'Percentage No'
FROM question AS q INNER JOIN answer AS a ON q.id = a.question_id WHERE
q.id < 192
GROUP BY q.id ORDER BY q.id ASC

I have a table of questions then a table of what the answer is (either
yes/no in the answer column). I count the amount of y values, then the
n values, then count all of them for a total, then I have to do the
same counts again to acheive a percentage.

Jul 5 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
What do you actually want to know?

Assuming you just want no. of yeses expressed as a % of the total, I
think that would be as follows (warning: this is off the top of my head
so it might not be quite right...)

SELECT (

SELECT COUNT( * )
FROM question q
LEFT JOIN answer a ON a.question_id = q.id
WHERE a.answer = 'y'
) / COUNT( * ) *100pct_yes
FROM questions;


si***@xiano.co.uk wrote:
Hi

I have a SQL query that counts values in a table and totals them up and
works out a percentage, it involves a few counts, is there a better why
to acheive what I want other than the way I have done it?

The query is:

SELECT q.`text` AS 'Question',
(SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer = 'y')
AS 'Yes',
(SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer = 'n')
AS 'No',
(SELECT COUNT(*) FROM answer WHERE question_id = q.id) AS 'Total',
((SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer =
'y') /
((SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer =
'y') + (SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer
= 'n')) * 100) AS 'Percentage Yes',
((SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer =
'n') /
((SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer =
'y') + (SELECT COUNT(*) FROM answer WHERE question_id = q.id AND answer
= 'n')) * 100) AS 'Percentage No'
FROM question AS q INNER JOIN answer AS a ON q.id = a.question_id WHERE
q.id < 192
GROUP BY q.id ORDER BY q.id ASC

I have a table of questions then a table of what the answer is (either
yes/no in the answer column). I count the amount of y values, then the
n values, then count all of them for a total, then I have to do the
same counts again to acheive a percentage.
Jul 5 '06 #2

P: n/a
si***@xiano.co.uk wrote:
Hi

I have a SQL query that counts values in a table and totals them up and
works out a percentage, it involves a few counts, is there a better why
to acheive what I want other than the way I have done it?
I think the following gives the same results, and runs faster:

SELECT q.`text` AS 'Question',
SUM(a.answer = 'y') AS 'Yes',
SUM(a.answer = 'n') AS 'No',
COUNT(*) AS 'Total',
SUM(a.answer = 'y')/COUNT(*) AS 'Percentage Yes',
SUM(a.answer = 'n')/COUNT(*) AS 'Percentage No'
FROM question AS q INNER JOIN answer AS a ON q.id = a.question_id
WHERE q.id < 192
GROUP BY q.id
ORDER BY q.id ASC

Regards,
Bill K.
Jul 5 '06 #3

P: n/a
Thanks Bill,

That is a hell of a lot quicker.......I am always keen to optimize and
improve my SQL queries.

Thanks again
Simon
Bill Karwin wrote:
si***@xiano.co.uk wrote:
Hi

I have a SQL query that counts values in a table and totals them up and
works out a percentage, it involves a few counts, is there a better why
to acheive what I want other than the way I have done it?

I think the following gives the same results, and runs faster:

SELECT q.`text` AS 'Question',
SUM(a.answer = 'y') AS 'Yes',
SUM(a.answer = 'n') AS 'No',
COUNT(*) AS 'Total',
SUM(a.answer = 'y')/COUNT(*) AS 'Percentage Yes',
SUM(a.answer = 'n')/COUNT(*) AS 'Percentage No'
FROM question AS q INNER JOIN answer AS a ON q.id = a.question_id
WHERE q.id < 192
GROUP BY q.id
ORDER BY q.id ASC

Regards,
Bill K.
Jul 10 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.