Row count based on column value - How can I do this? 1

[southbeach]

Given tableA and colSize, I need a row count for each value in colSize.

Values can be 10, 20 and 40

tableA has a unique key that I intend to use to match my query against.

So, if I had rows

key colSize
----+--------
1 10
2 40
3 20
4 20
5 40

I expect counts to be

Count Size
-----+------
1 10
2 20
2 40

I know of COUNT(*) but I cannot figure out how to do multiple count and conditions within a single query.

Thank you all for your help!

 

[southbeach]

OK - I think I came up with a solution

 SELECT * ,
CASE WHEN size = 40 THEN COUNT( * ) END AS count40,
CASE WHEN size = 20 THEN COUNT( * ) END AS count20,
CASE WHEN size = 10 THEN COUNT( * ) END AS count10
FROM tableA WHERE key = value GROUP BY size

Not sure how expensive this is, but for now, it is all I got.

 

[ethorn10]

Give this a try:

SELECT count(*)
       , colSize
FROM tableA
GROUP BY colSize

 

[southbeach]

This works if I only wanted a quick query without assigning the respective counts to a specific variable.

You see, I am using this query within a PHP application and need to reference the count values.

Thanks!

 

[ethorn10]

I'd suggest using the query I provided and ask in the PHP forum how to better assign the variable based on the query results. It seems you should be able to do some checks in PHP (I'm not a PHP guy) to determine what colSize is and assign the count(*) to a variable.

 

[Kozusnik]

$query = "
SELECT colSize, count(*)
FROM tableA
GROUP BY colSize";

$result = mysql_query($query) or die( mysql_error());

$data = array();
while ($rows = mysql_fetch_row($result)){
$size = $rows[0];
$count = $rows[1];

$data[$size] = $count;
}

To retrieve the values...

echo $data[40] . "\n";
echo $data[10] . "\n";

Or use foreach...

foreach ($data as $k=>$v){
echo "Size=$k and Count=$v \n";
}

Returns...

2
1

Mark

 

[southbeach]

Very nice Mark - Thanks!