Number of foods that scored "true" in being good, grouped by culture SQL-CodePudding

Okay, so I've been driving myself crazy trying to get this to display in SQL. I have a table that stores types of food, the culture they come from, a score, and a boolean value about whether or not they are good. I want to display a record of how many "goods" each culture racks up. Here's the table (don't ask about the database name):

So I've tried:

SELECT count(good = 1), culture FROM animals_db.foods group by culture;

SELECT count(good = true), culture FROM animals_db.foods group by culture;

But it doesn't present the correct results, it seems to include anything that has any "good" value (1 or 0) at all.

How do I get the data I want?

CodePudding user response：

If the purpose is to count the number of good=1 for each culture, this works:

select culture,
       count(*)
  from foods
 where good=1
 group by 1
 order by 1;

Result:

culture |count(*)|
-------- -------- 
        |       1|
American|       1|
Chinese |       1|
European|       1|
Italian |       2|

The reason your first query doesn't return the result can be explained as below:

select culture,
       good=1  as is_good
  from foods
 order by 1;

You actually get:

culture |is_good|
-------- ------- 
        |      1|
American|      0|
American|      1|
Chinese |      1|
European|      1|
French  |      0|
French  |      0|
German  |      0|
Italian |      1|
Italian |      1|

After applied group by culture and count(good=1), you're actually counting the number of NOT NULL values in good=1. For example:

select culture,
       count(good=0)  as c0,
       count(good=1)  as c1,
       count(good=2)  as c2,
       count(good)    as c3,
       count(null)    as c4
  from foods
 group by culture
 order by culture;

Outcome:

culture |c0|c1|c2|c3|c4|
-------- -- -- -- -- -- 
        | 1| 1| 1| 1| 0|
American| 2| 2| 2| 2| 0|
Chinese | 1| 1| 1| 1| 0|
European| 1| 1| 1| 1| 0|
French  | 2| 2| 2| 2| 0|
German  | 1| 1| 1| 1| 0|
Italian | 2| 2| 2| 2| 0|

Update: This is similar to your question: Is it possible to specify condition in Count()?.

CodePudding user response：

instead of count , use sum.

SELECT sum(good), culture FROM animals_db.foods group by culture; -- assume good column value have integer data type and good value is represent as 1 otherwise 0

or other way is using count

select count( case when good=1 then 1  end) , culture from animals_db.foods group by culture;