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