SQL get total sellers of product category

I have like this database tables:

Table categories

id
title
description
status

Table products

id
user_id
category_id
description
status

Table product_varieties

id
variety_id
product_id
description
wholesale_price
retail_price
status
total

I have query to get price min, max and avg in each category:

SELECT `categories`.`title`,
         min(product_varieties.wholesale_price) AS wholesalePriceMin,
         max(product_varieties.wholesale_price) AS wholesalePriceMax,
         avg(product_varieties.wholesale_price) AS wholesalePriceAvg,
         min(product_varieties.retail_price) AS retailPriceMin,
         max(product_varieties.retail_price) AS retailPriceMax,
         avg(product_varieties.retail_price) AS retailPriceAvg
FROM `products`
LEFT JOIN `categories`
    ON `categories`.`id` = `products`.`category_id`
LEFT JOIN `product_varieties`
    ON `product_varieties`.`product_id` = `products`.`id`
WHERE `products`.`id` IN (1,2,3,4)
GROUP BY  `products`.`category_id`

Now I need get total users (sellers) of each category by editing my current query.

I tried like this:

SELECT `categories`.`title`,
         min(product_varieties.wholesale_price) AS wholesalePriceMin,
         max(product_varieties.wholesale_price) AS wholesalePriceMax,
         avg(product_varieties.wholesale_price) AS wholesalePriceAvg,
         min(product_varieties.retail_price) AS retailPriceMin,
         max(product_varieties.retail_price) AS retailPriceMax,
         avg(product_varieties.retail_price) AS retailPriceAvg,
         
    (SELECT user_id,
         COUNT(*)
    FROM products
    GROUP BY  category_id) AS totalUsers
FROM `products`
LEFT JOIN `categories`
    ON `categories`.`id` = `products`.`category_id`
LEFT JOIN `product_varieties`
    ON `product_varieties`.`product_id` = `products`.`id`
WHERE `products`.`id` IN (1,2,3,4)
GROUP BY  `products`.`category_id`

But my query not work and return error with message:

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

How I can correct my query?

Answer

A “correlated subquery” can be used in the select list of your main query, this query must return just a single value and the “correlation” occurs through the where clause in that some data from the main query is used to locate the wanted data in the subquery.

SELECT
      `categories`.`title`
    , min(product_varieties.wholesale_price) AS wholesalePriceMin
    , max(product_varieties.wholesale_price) AS wholesalePriceMax
    , avg(product_varieties.wholesale_price) AS wholesalePriceAvg
    , min(product_varieties.retail_price) AS retailPriceMin
    , max(product_varieties.retail_price) AS retailPriceMax
    , avg(product_varieties.retail_price) AS retailPriceAvg
    , (
        SELECT COUNT(*)
        FROM products AS p
        WHERE p.category_id = `products`.`category_id`
        ) AS totalUsers
FROM `products`
LEFT JOIN `categories` ON `categories`.`id` = `products`.`category_id`
LEFT JOIN `product_varieties` ON `product_varieties`.`product_id` = `products`.`id`
WHERE `products`.`id` IN (1, 2, 3, 4)
GROUP BY `products`.`category_id`

but I suspect this can be further simplified:

SELECT
      `categories`.`title`
    , min(product_varieties.wholesale_price) AS wholesalePriceMin
    , max(product_varieties.wholesale_price) AS wholesalePriceMax
    , avg(product_varieties.wholesale_price) AS wholesalePriceAvg
    , min(product_varieties.retail_price) AS retailPriceMin
    , max(product_varieties.retail_price) AS retailPriceMax
    , avg(product_varieties.retail_price) AS retailPriceAvg
    , COUNT(products.user_id) AS totalUsers
FROM `products`
LEFT JOIN `categories` ON `categories`.`id` = `products`.`category_id`
LEFT JOIN `product_varieties` ON `product_varieties`.`product_id` = `products`.`id`
WHERE `products`.`id` IN (1, 2, 3, 4)
GROUP BY `products`.`category_id`

or, depending on the data:

    , COUNT(DISTINCT products.user_id) AS totalUsers