A count and a case statement

this is the script im trying to do but it doesn’t seem to work because i get MSG 8120 which is no aggregate function

Select count(*) as number_of_animals,
case when
         animalKind in ('Sheep','Goats','Cow') then 'red meat'
         animalKind in ('Chicken', 'turkey', 'Fish') then 'white meat'
end as Meat_Type
from farming

To check the above query I created a table with name farming.
Inside farming table I insert one column animalKind .
for the table farming above query retuns number_of_animals and Meat_Type
number_of_animals=total number of rows in the table and Meat_Type = according to the first row value
since I have created 6 rows and the value of first row is Sheep so above query return number_of_animals=6 and Meat_Type="red meat".

I don’t know why are you using and in ‘Sheep’ and in other . if you copying the same query it will return error .so use single quote i.e. ' instead of .

@FatmaNagori The forum created the formatted quotes you’re seeing, the single quotes are correct in the script. SQL only allows single quotes for string literals.

@NaomiS your first column is an aggregate, your second is not, meaning it will output a value for every row. Are you trying to get totals of meat types? You’ll need to add a GROUP BY Meat_Type at the end if so, and change your count(*) to count(Meat_Type)

1 Like

yes, but meat_type is a case statement created column. let me try that and i’ll come back to you

Calculated columns are as first-class as any other. Whether it allows you to use the column name in the group by clause is another matter – some databases will make you type it all out again. In that case I usually reach for a sub-select.

I’ve edited your post for readability. When you enter a code block into a forum post, please precede it with a separate line of three backticks and follow it with a separate line of three backticks to make it easier to read.

See this post to find the backtick on your keyboard. The “preformatted text” tool in the editor (</>) will also add backticks around text.

Note: Backticks are not single quotes.


I’ve just tried that, i returns invalid column name…it doesn’t seem to recognize the column in the case statement

how does a subselect work?

since I always use mariadb for sql and mariadb allows double quote by default.
so i was never know that sql not permit double quote.

I tried your query in Mysql 5.6, and did not get any sort of error, even in ANSI sql mode, though I did only get one row back, as I had expected. To get a grouping by Meat_Type, I simply had to add GROUP BY Meat_Type at the end of the query, and it worked out of the box.

Which database are you using, and which version?

As for double quotes, don’t ever assume what mysql/maria does is anything like standard. Mysql is the PHP of databases, and I don’t mean that as any kind of compliment.