0

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):

enter image description here

So I've tried:

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

Or

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.

enter image description here

How do I get the data I want?

DennisM
  • 359
  • 1
  • 3
  • 13
  • Please read and follow this: https://stackoverflow.com/help/minimal-reproducible-example and this: https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors – Jonas Metzler Dec 04 '22 at 04:04
  • Please also add the expected outcome. It's unclear if you want to list cultures without goods as culture,0 or if they should not appear in your result. – Jonas Metzler Dec 04 '22 at 04:30

2 Answers2

1

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;
Learn Hadoop
  • 2,760
  • 8
  • 28
  • 60
0

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()?.

JHH
  • 1,331
  • 1
  • 2
  • 9
  • Refusing using column names in GROUP BY and ORDER BY is very bad practice. We must hope the question's author will keep using MYSQL, otherwise this will even fail to be executed: https://dbfiddle.uk/SGDWRwOz – Jonas Metzler Dec 04 '22 at 05:07