Add a subquery to SELECT statement that depends on the first column?

Question

Is there a way I can write this query in a way to get the result of my sub query compared with my category_id.

SELECT category_id,
count(id) as TOTAL COUNT, 
(select count(*) from products where product_path LIKE '%Electronics%'
 and category_id = category_id ) as ELECTRONIC COUNT
FROM products
WHERE product_path LIKE '%Products%'
GROUP BY category_id

I want the Results in the bellow manner:

"category_id"   "TOTAL COUNT"   "ELECTRONIC COUNT"
   "173"              "1"               "243"
    "42"              "1"               "243"
   "211"              "41"              "243"
   "162"              "10"              "243"
   "172"              "139"             "243"
   "116"              "54"              "243"
    "10"              "3"               "243"

I want the electronic count to be dependent on category. I.e., first row should be where category_id = 173, 2nd one where category_id = 42, 3rd should be where category_id = 211 etc.

Answer 1

To make your correlated subquery work with the same table, you would have to use table aliases:

SELECT category_id
      ,count(*) AS total_count  -- unquoted column alias with space is wrong, too
      ,(SELECT count(*)
        FROM   products AS p1
        WHERE  product_path LIKE '%Electronics%'
        AND    p1.category_id = p.category_id
       ) AS electronic_count
FROM   products AS p
WHERE  product_path LIKE '%Products%'
GROUP  BY category_id;

Assuming id is the primary key and, as such, NOT NULL. Then count(*) does a better job.

But this can be further simplified to:

SELECT category_id
      ,count(*) AS total_count -- keyword AS is needed for column alias
      ,count(product_path LIKE '%Electronics%' OR NULL) AS electronic_count
FROM   products p  -- keyword AS is just noise for table alias
WHERE  product_path LIKE '%Products%'
GROUP  BY category_id;

Much faster.
count() only counts non-null values. By adding OR NULL I convert FALSE to NULL. Thereby only those rows count, where product_path LIKE '%Electronics%' evaluates to TRUE.