query help needed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Jay
Forum Newbie
Posts: 22
Joined: Fri Jul 12, 2002 8:36 am
Location: KUL, Malaysia

query help needed

Post 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.
EricS
Forum Contributor
Posts: 183
Joined: Thu Jul 11, 2002 12:02 am
Location: Atlanta, Ga

Query

Post 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
Jay
Forum Newbie
Posts: 22
Joined: Fri Jul 12, 2002 8:36 am
Location: KUL, Malaysia

Post 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)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
EricS
Forum Contributor
Posts: 183
Joined: Thu Jul 11, 2002 12:02 am
Location: Atlanta, Ga

Try this.

Post 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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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.
EricS
Forum Contributor
Posts: 183
Joined: Thu Jul 11, 2002 12:02 am
Location: Atlanta, Ga

Oops

Post by EricS »

I forgot the "FROM tbl_name", but fortunately mikeq didn't. Thanks.
Jay
Forum Newbie
Posts: 22
Joined: Fri Jul 12, 2002 8:36 am
Location: KUL, Malaysia

Post by Jay »

thank you for the solution :)

here's what u guys help complete - GIDTopsites

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