1

I want to do a count on a certain value in a column. Structure looks like this;

FieldName       FieldValue
food            fruit
food            fruit
food            fruit
food            vegetable

MySQL query looks like this:

SELECT `FieldValue` AS `type`,
COUNT(case when `FieldValue` = 'fruit' then 1 end)AS `counts`
FROM `mytable`
WHERE `FieldName` LIKE '%food%' 
GROUP BY `FieldName`

And it returns this what is OK:

type        counts
fruit       3

But when i change "fruit" into "vegetable" :

SELECT `FieldValue` AS `type`,
COUNT(case when `FieldValue` = 'vegetable' then 1 end)AS `counts`
FROM `mytable`
WHERE `FieldName` LIKE '%food%' 
GROUP BY `FieldName`

I get this back:

type        counts
fruit       1

The count is OK but the type is wrong it should be 'vegetable'. What am i doing wrong here? Maybe the solution is very simple but i just dont see it.

user3676298
  • 55
  • 1
  • 8
  • You need to check Fieldvalue in where condition also there is no need to use case when then here – dev Oct 12 '16 at 12:19

1 Answers1

3

Try:

SELECT `FieldValue` AS `type`,
COUNT(*)AS `counts`
FROM `mytable`
WHERE `FieldName` LIKE '%food%' and `FieldValue` = 'vegetable'
GROUP BY `FieldValue`

Notice that in your original query you were grouping by FieldName and not FieldValue, while FieldValue is the field you were using in the select clause.

If you are using MySQL, you should be aware that it will allow you to use aggregate functions where the non-aggregated columns aren't in the group by clause. This will throw an error in other RDBMS like Oracle and MS SQL Server, but in MySQL it just leaves you wondering what's going on.

MySQL Aggregate Functions without GROUP BY clause

Community
  • 1
  • 1
CLAbeel
  • 1,078
  • 14
  • 20
  • Yes this one did work. I am only using MySQL. I didn't know i could use the 'value' in the WHERE clause. A big thank you! – user3676298 Oct 12 '16 at 13:34