Stack Exchange
Without hardcore value in CASE (SQL or knex )
i had done for my logic, but it is a hardcore value for Classiky and Stationery, is any way i able to do after filtering.
SELECT 
    b.name AS brand_name,
    c.name AS category_name,
    sum(case when b.name = 'Classiky' then 1 else 0 end) AS brandCount,
    sum(case when c.name = 'Stationery' then 1 else 0 end) AS categoryCount
FROM products  p
JOIN categories_products__products_categories  cp
    ON cp.product_id = p.id
JOIN categories c
    ON category_id = c.id 
LEFT JOIN brands b
    ON brand = b.id
WHERE category_slug = "queryFromURL"
GROUP BY brand_name, category_name 

Above SQL Statement output:
+----------------------------------------------------------+
|  brand_name | category_name | brandCount | categoryCount |
+-------------+---------------+------------+---------------+
|  Classiky   |    Tapes      |     2      |       0       |
|   SARASA    |   Stationery  |     0      |       2       |
|   Paper     |    Notes      |     0      |       0       |
+-------------+---------------+------------+---------------+

Since i am hardcore value for it, so the result will look like above table.
As example: i have data of brand and category in category after filtering

Classiky   (brand)
SARASA     (brand)
Paper      (brand)
Tapes      (category)
Stationery (category)
Notes (category)

i wanna to count how many i have after filtering in brandCount, same as categoryCount instead of hardcore the value for it.
what i expected output: (eg: there has "Tapes category" for 3 after filtered)
+----------------------------------------------------------+
|  brand_name | category_name | brandCount | categoryCount |
+-------------+---------------+------------+---------------+
|  Classiky   |    Tapes      |     2      |       3       |
|   SARASA    |   Stationery  |     4      |       2       |
|   Paper     |    Notes      |     6      |       4       |
+-------------+---------------+------------+---------------+

I using knex for this sql. But it is more understanding for us, so i write in raw sql. Hope someone can help me.

Without sample data, it is really hard to follow your issue.  If you set up a db/sql fiddle of some sort, your question might be clearer.

Are you looking for something like this?
count(b.id) AS brandCount,
count(distinct c.id) AS categoryCount


yes. Really appreciate for your help. it work and surprise me, it is only two line of code.
