Q. Category Product Count
| category | products |
|---|---|
| Electronics | TV, Radio, Laptop |
| Furniture | Chair |
| Clothing | Shirt, Pants, Jacket, Shoes |
| Groceries | Rice, Sugar |
Count the number of products in each category (It is assumed to be an Easy level question So maybe start with an easier rather than using user defined functions straight away)
All answers are appreciated though.
<code>select category, count(CHAR_LENGTH(products) ) AS product_count
from categories
group by category
order by count(*) asc
</code>
<code>select category, count(CHAR_LENGTH(products) ) AS product_count
from categories
group by category
order by count(*) asc
</code>
select category, count(CHAR_LENGTH(products) ) AS product_count
from categories
group by category
order by count(*) asc
6
Your SQL query is close but not correct. The count(CHAR_LENGTH(products)) part does not give you the correct number of products in each category.
Here is the simple approach.
Since the product is comma separated in the product columns, we can use COUNT function in combination with FIND_IN_SET to count the number of products.
Solution 1: without FIND_IN_SET
<code>SELECT
category,
LENGTH(products) - LENGTH(REPLACE(products, ',', '')) + 1 AS product_count
FROM
categories
GROUP BY
category
ORDER BY
product_count ASC;
</code>
<code>SELECT
category,
LENGTH(products) - LENGTH(REPLACE(products, ',', '')) + 1 AS product_count
FROM
categories
GROUP BY
category
ORDER BY
product_count ASC;
</code>
SELECT
category,
LENGTH(products) - LENGTH(REPLACE(products, ',', '')) + 1 AS product_count
FROM
categories
GROUP BY
category
ORDER BY
product_count ASC;
Solution2: With FIND_IN_SET
<code>SELECT
category,
SUM(FIND_IN_SET(product, products) > 0) AS product_count
FROM
(SELECT
category,
SUBSTRING_INDEX(SUBSTRING_INDEX(products, ',', numbers.id), ',', -1) AS product
FROM
categories
CROSS JOIN
(SELECT id FROM numbers WHERE id <= (SELECT MAX(LENGTH(products) - LENGTH(REPLACE(products, ',', '')) + 1) FROM categories)) AS numbers
) AS subquery
GROUP BY
category
ORDER BY
product_count ASC;
</code>
<code>SELECT
category,
SUM(FIND_IN_SET(product, products) > 0) AS product_count
FROM
(SELECT
category,
SUBSTRING_INDEX(SUBSTRING_INDEX(products, ',', numbers.id), ',', -1) AS product
FROM
categories
CROSS JOIN
(SELECT id FROM numbers WHERE id <= (SELECT MAX(LENGTH(products) - LENGTH(REPLACE(products, ',', '')) + 1) FROM categories)) AS numbers
) AS subquery
GROUP BY
category
ORDER BY
product_count ASC;
</code>
SELECT
category,
SUM(FIND_IN_SET(product, products) > 0) AS product_count
FROM
(SELECT
category,
SUBSTRING_INDEX(SUBSTRING_INDEX(products, ',', numbers.id), ',', -1) AS product
FROM
categories
CROSS JOIN
(SELECT id FROM numbers WHERE id <= (SELECT MAX(LENGTH(products) - LENGTH(REPLACE(products, ',', '')) + 1) FROM categories)) AS numbers
) AS subquery
GROUP BY
category
ORDER BY
product_count ASC;
How it will work
LENGTH(products)gives us the total length of the products.LENGTH(REPLACE(products, ',', ''))gives us the length of the products string with all commas removed.- By Subtracting the two lengths, we get the number of commas in the product string.
- Adding 1 to the result gives us the total count of the products in each category.
6