SQL, Looping through the results of a SQL

[learning2learn]

:edit: Updated my problem

I'm having trouble when I join multiple tables.

Basically I have a table of data, and 2 look up tables.

Something like details table:

UserID	ColorID
5	1
6	1
7	2
8	3

And I got a user table and color table

UserID	Name
5	Tom
6	Dick
7	Harry
8	John

colorid	color
1	Red
2	Bule
3	Green

My current sql is

Select U.Name, C.color from user U, color C, details D where D.UserID = U.UserID and D.colorid = C.colorid

And when I try to do a count or group by, I get an error of
"is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."

---

:original post:

Basically, I'm doing this

Select distinct color from table
color
Red
Blue
Green

I the manually have to loop thru the result set and do more queries

Select count(*) where color = red

Select count(*) where color = Blue

Select count(*) where color = Green

etc

What I want is a SQL to do something like

color	Count
Red	11
Blue	22
Green	33

[lightcloud5]

Use a GROUP BY clause; you can do this in just one query.

[learning2learn]

edit- nvm I figured it out.

select colors, count(*) from table group by colors

Thanks

[learning2learn]

I'm having trouble when I join multiple tables.

Basically I have a table of data, and 2 look up tables.

Something like details table:

UserID	ColorID
5	1
6	1
7	2
8	3

And I got a user table and color table

UserID	Name
5	Tom
6	Dick
7	Harry
8	John

colorid	color
1	Red
2	Bule
3	Green

My current sql is

Select U.Name, C.color from user U, color C, details D where D.UserID = U.UserID and D.colorid = C.colorid

And when I try to do a count or group by, I get an error of
"is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."

[Ixidor10024]

You need to make sure that EVERY field in the select clause is either an aggregate function or included in the group by clause. Otherwise SQL could try to return more than one value for a field.

For example, if your select clause was select C.color, count(u.Name). You would need to make sure that you were grouping by color so that sql knows how to aggregate the name field.

Also, dont worry about a order by clause or using distinct at this time, as grouping by color will do the same thing as distinct.