469,582 Members | 2,483 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,582 developers. It's quick & easy.

Dodgy SQL Query

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
3 3124
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
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
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.

Similar topics

2 posts views Thread by jaysonsch | last post: by
9 posts views Thread by netpurpose | last post: by
3 posts views Thread by Harvey | last post: by
4 posts views Thread by Diamondback | last post: by
5 posts views Thread by WindAndWaves | last post: by
8 posts views Thread by Duncan Winn | last post: by
8 posts views Thread by Tomás | last post: by
2 posts views Thread by voidtwerp | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.