Dodgy SQL Query

[simon]

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.

[zac.carey]

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.

[Bill Karwin]

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.

[Bowen]

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.