Page 1 of 1

query help needed

Posted: Fri Jul 12, 2002 8:36 am
by Jay
i am trying to do this for about an hour now but i think i am becoming obsessed to a point i can't think clearly now...

i have a table

Code: Select all

id  |  colour  |  yn
--------------------
1   |  green   | y
2   |  red     | y
3   |  green   | n
4   |  green   | y
5   |  blue    | y
6   |  red     | y
7   |  red     | n
8   |  yellow  | y
9   |  green   | n
10  |  green   | y
now what i wanna do is get a total number 'y's grouped by 'colour' and finally ordered by 'total_y'; ... a bit like this:

Code: Select all

green  | 3
red    | 2
blue   | 1
yellow | 1
Thanks for any help/suggestion.

Query

Posted: Fri Jul 12, 2002 9:10 am
by EricS
Something like this should do it.

SELECT colour, count(id) as total_y WHERE yn = "y" GROUP BY Colour ORDER BY total_y;

Hope that helps

Posted: Fri Jul 12, 2002 10:18 am
by Jay
u would think that, wouldn't u?

BUT (and unless my MySQL is really funky) it didn't work, instead i got it ordered by 'colour' doing it the way u described it (which is also what i tried before getting to this board in desperation)

Posted: Fri Jul 12, 2002 12:31 pm
by twigletmac
Not sure but you could try turning the WHERE statement into a HAVING statement:

Code: Select all

SELECT colour, count(id) as total_y GROUP BY Colour HAVING yn = "y" ORDER BY total_y;
Mac

Try this.

Posted: Fri Jul 12, 2002 12:34 pm
by EricS
Does this change anything?

Code: Select all

SELECT colour, count(id) as total_y WHERE yn = "y" GROUP BY Colour ORDER BY total_y DESC;
Note the DESC attribute after the ORDER BY Clause.

Also, if it doesn't work, give us the results of the query and we might be able to figure it out.

Posted: Fri Jul 12, 2002 12:40 pm
by mikeq
I tried this

SELECT colour, count(id) as total_y
from colours
WHERE yn = 'y'
GROUP BY Colour
ORDER BY total_y DESC;

and it worked fine, gave the answer you wanted.

Damn, EricS beat me by 6 minutes.

Oops

Posted: Fri Jul 12, 2002 12:55 pm
by EricS
I forgot the "FROM tbl_name", but fortunately mikeq didn't. Thanks.

Posted: Sun Jul 14, 2002 11:29 am
by Jay
thank you for the solution :)

here's what u guys help complete - GIDTopsites

not a pretty 'sight' i know but getting there...