Hi,

I want to query this table (just a sample):

Name : varchar
Black: boolean;
Blue: boolean;
PercentageBlack: float;
PercentageBlue: float;

and would like to get this result:

Name   Total   CountOfBlack   CountOfBlue   PercentageBlack   PercentageBlue

xy       300    60                    240                 20
       80


Generally no problem, but: Is it possible to to that in one query?

Regards Uli

Uli,

<< I want to query this table (just a sample):

Name : varchar
Black: boolean;
Blue: boolean;
PercentageBlack: float;
PercentageBlue: float;

and would like to get this result:

Name Total CountOfBlack CountOfBlue PercentageBlack PercentageBlue >>

Try this:

SELECT Name,
COUNT(*) AS Total,
COUNT(IF(Black,1,0)) AS CountOfBlack,
COUNT(IF(Blue,1,0)) AS CountOfBlue
FROM MyTable
GROUP BY Name

However, I'm not sure what you want to do with the percentages - average
them ?  Also, are the percentages actually in the source table ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim
> SELECT Name,
> COUNT(*) AS Total,
> COUNT(IF(Black,1,0)) AS CountOfBlack,
> COUNT(IF(Blue,1,0)) AS CountOfBlue
> FROM MyTable
> GROUP BY Name

Looks good, but doesn't seem to work. CountOfBlack and CountOfBlue have
the same value as Total.

> However, I'm not sure what you want to do with the percentages - average
> them ?  Also, are the percentages actually in the source table ?

Just calculated values, not important for the query, I should have
omitted them.

Uli

Tim,

how about this:

select Name, count(*) as Total,
 sum(cast(black as integer)) as CountBlack,
 sum(cast(blue as integer)) as CountBlue
 from test
 group by name;

Seems to do the trick.

Uli

Uli,

<< Looks good, but doesn't seem to work. CountOfBlack and CountOfBlue have
the same value as Total. >>

Duh, I used COUNT() when I should have used SUM():

SELECT Name,
COUNT(*) AS Total,
SUM(IF(Black,1,0)) AS CountOfBlack,
SUM(IF(Blue,1,0)) AS CountOfBlue
FROM MyTable
GROUP BY Name

or you could use this:

SELECT Name,
COUNT(*) AS Total,
COUNT(IF(Black,Black,NULL)) AS CountOfBlack,
COUNT(IF(Blue,Blue,NULL)) AS CountOfBlue
FROM MyTable
GROUP BY Name

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim
> Duh, I used COUNT() when I should have used SUM():

And with sum() it works fine, off course. (Should have figured that
out myself!).

Regards Uli