MySQL/MariaDB Count not output all result of WHERE IN clause of array-CodePudding

I need help on this. Will really appreciate it. I am sending an array from an endpoint to my model to fetch the count of each item in my array, If I do a simple selection of all or some of the columns in my table, I get all the results. If I combine a selected column and the count() I get the count of all the items in the table

$sql = "SELECT barcode, (SELECT count(barcode) bcnt FROM shelves) as bcount FROM shelves WHERE barcode IN ('".implode("','",$data['barcode'])."')";
            $statement = $this->connection->prepare($sql);
            $statement->execute();                      
            $row = $statement->fetchAll();          
            $this->response['data']=$row;
            $this->response['error_code']=0;
            $this->response['status']='success';
            $this->response['message']='data retrieve for shelves';

{
    "error_code": 0,
    "status": "success",
    "message": "data retrieve for shelves",
    "error_message": "",
    "data": [
        {
            "barcode": "5902280031062",
            "bcount": "1485"
        },
        
        {
            "barcode": "5902280031062",
            "bcount": "1485"
        },
        {
            "barcode": "5902280031062",
            "bcount": "1485"
        },
        {
            "barcode": "5902280031062",
            "bcount": "1485"
        },
        {
            "barcode": "5902280031062",
            "bcount": "1485"
        },
        
        {
            "barcode": "5902280031062",
            "bcount": "1485"
        },
        {
            "barcode": "5902280031062",
            "bcount": "1485"
        },
        
        {
            "barcode": "133",
            "bcount": "1485"
        }
    ]
}

if I use count() only I get only one result.

$sql="SELECT count(*) FROM shelves WHERE barcode IN ('".implode("','",$data['barcode'])."')";

{
    "error_code": 0,
    "status": "success",
    "message": "data retrieve for shelves",
    "error_message": "",
    "data": [
        {
            "count(*)": "15"
        }
    ]
}

CodePudding user response：

You need to use GROUP BY when counting so SQL can properly count for each barcode.

In your first example it is counting all barcodes in separate SELECT and displays only the ones that are in IN() condition. And then it returns that full count for every row where barcodes in IN() condition exist.

In second example you are actually counting it fine but you are not returning barcodes. To do that you need to select and group them.

Your query would then look something like this:

$sql = "
  SELECT barcode, count(*) AS bcount
  FROM shelves
  WHERE barcode IN ('".implode("','",$data['barcode'])."')
  GROUP BY barcode
  ORDER BY bcount DESC
";