multiple mysql count(), ERROR 1242 (21000): Subquery returns more than 1 row-CodePudding

I have three tables, products, review, and product_wishlist. After getting the number of reviews and wishlists of products with mysql COUNT(), I want to list them in order of wishlist count. I've tried using the subquery as shown below, but this error occurs.

ERROR 1242 (21000): Subquery returns more than 1 row

What am I doing wrong? I need help.

const popularSort = async (name, type, page) => {
  const sort = await myDataSource.query(
    `SELECT
      c.name AS category,
      t.name AS type,
      p.name,
      p.description,
      (SELECT COUNT(w.product_id)
        FROM products p
        LEFT OUTER JOIN product_wishlist w
        ON p.id = w.product_id
        GROUP BY p.id) AS likeCount, 
      COUNT(r.product_id) AS reviewCount,
      p.price_origin,
    FROM products p
    JOIN category c ON c.id = p.category_id
    JOIN product_types t ON t.id = p.type_id
    LEFT OUTER JOIN review r ON p.id = r.product_id
    WHERE c.name = ? AND t.name = ?
    GROUP BY p.id
    ORDER BY likeCount DESC
    LIMIT ?, 9`,
    [name, type, (page - 1) * 9]
  );
  return sort;
};

CodePudding user response：

This will return more than one row as result ,which leads to your error

  (SELECT COUNT(w.product_id)
    FROM products p
    LEFT OUTER JOIN product_wishlist w
    ON p.id = w.product_id
    GROUP BY p.id) AS likeCount

So oyu need to rewrite your query

So that the subquery returns only one row and value

const popularSort = async (name, type, page) => {
  const sort = await myDataSource.query(
    `SELECT
      c.name AS category,
      t.name AS type,
      pr.name,
      pr.description,
      (SELECT COUNT(w.product_id)
        FROM products p
        LEFT OUTER JOIN product_wishlist w
        ON p.id = w.product_id
        WHERE p.id = pr.id) AS likeCount, 
      COUNT(r.product_id) AS reviewCount,
      pr.price_origin,
    FROM products pr
    JOIN category c ON c.id = pr.category_id
    JOIN product_types t ON t.id = pr.type_id
    LEFT OUTER JOIN review r ON pr.id = r.product_id
    WHERE c.name = ? AND t.name = ?
    GROUP BY pr.id
    ORDER BY likeCount DESC
    LIMIT ?, 9`,
    [name, type, (page - 1) * 9]
  );
  return sort;
};