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.

Re: Dodgy SQL Query

simon@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.

Re: Dodgy SQL Query

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;




simon@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.

Re: Dodgy SQL Query

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:
> simon@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.