I have a SELECT SQL that returns the following result:

Gender Number

Female: 15967
Male: 19739

The SQL is as follows:

SELECT gender, COUNT(gender) AS number FROM links GROUP BY gender ORDER BY gender


Now what I want hopefully should be pretty easy. I want to be able to associate the Gender results with a particular color. So for example, the result I need is:

Gender Number Color

Female: 15967 #62ACD7
Male: 19739 #38DA9F

This should be able to be accomplished without creating another field in the links table called Color. This should all fall within the SELECT query I think??? Also, I need for this to also work with queries that return more than 2 rows, i.e.

Country: Number Color

Albania: 24 #62ACD7
Algeria: 37 #38DA9F
Argentina: 15 #38DA3C
Australia: 272 #C9DA38


Thank you so much. If there is some php programming required to get this result, then that would work fine as well, but I am hoping this can all be accomplished with the SELECT query.

Robert

Hi Robgo.

Using another table that explicitly defines the association between your first column and colour value and then applying SELECT is probably the most common. It may not be what you after but practical.

CREATE TABLE colour (
type VARCHAR(20) NOT NULL PRIMARY KEY,
value CHAR(7) NOT NULL
);
INSERT INTO colour VALUES('Female', '#62ACD7');
INSERT INTO colour VALUES('Male', '#38DA9F');

SELECT l.gender, COUNT(l.gender) AS number, c.value FROM links as l, colour as c GROUP BY gender ORDER BY gender WHERE l.gender=c.type;


If colour value is not dependent on the first column (i.e. you just want ppl to read more clearly), you can simply colour it up in your PHP or other programming code when handling output of your SELECT query.

$colours=array("#62ACD7", "#38DA9F", "#38DA3C", "#C9DA38"...);
$colours_len=count($colours);

mysql_connect(...);
$result=mysql_query("SELECT ...");
if($result){
$n=$mysql_num_rows($result);
for($i=0; $i<$n; $i++){
list($country, $number, $colour)=mysql_fetch_row($result);
print($count . ": " .$number . " " . $colours[$i % $colours_len]);
}
}

IMHO the nature of your intended task requires some level of iterating, not really SELECT's job. Hope that does you some help.

Hi,

I added the new table as suggested, but I keep getting errors using your SELECT query...

SQL-query:

SELECT l.gender, COUNT( l.gender ) AS number, c.value
FROM links AS l, colour AS c
GROUP BY gender
ORDER BY gender
WHERE l.gender = c.type
LIMIT 0 , 30
MySQL said:

#1064 - You have an error in your SQL syntax near 'WHERE l.gender=c.type LIMIT 0, 30' at line 1

I don't want to color the actual results, I just need the columb color to return in the results next to gender and number.

Thanks,

Robert

Oups, try this instead please.

This way we do the match between the result of gender count (2 rows) and colour (2 rows as well).