MySql count column based from same table

This type of questions already asked but that is not solve my problem.

I have table called scraping_data

+-----------------------------------------------------------+
| id		| accountId	| label			| subLabel	| status|
+-----------------------------------------------------------+
| 1			| 1			| Application	| Nice		| 1		|
| 2			| 1			| Application	| poor		| 1		|
| 3			| 1			| Application	| Nice		| 1		|
| 4			| 1			| Chennal		| Quality	| 1		|
| 5			| 1			| Application	| Nice		| 1		|
| 6			| 1			| Channel		| poor		| 1		|
+-----------------------------------------------------------+

Here I want to take counts by subLabel, for Example here label Application comes 4 times with Nice times and poor 1 time. And same Channel come 2 times with Quality 1 time, poor 1 time.

My output will be like:

Application Nice count 3 and poor count 1

query:

SELECT * FROM scraping_data

You can do it using the conditional aggregation using group by and sum() :

select label, sum(case when subLabel = 'Nice' then 1 else 0 end) as nice_count,
              sum(case when subLabel = 'poor' then 1 else 0 end) as poor_count
from mytable
group by label

Or using count() :

select label, count(case when subLabel = 'Nice' then 1 end) as nice_count,
              count(case when subLabel = 'poor' then 1 end) as poor_count
from mytable
group by label

To dynamically generate pivoted data you can use Prepared statements :

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when subLabel = ''',
      subLabel,
      ''' then 1 else 0 end) AS `',
      subLabel, '_count`'
    )
  ) INTO @sql
FROM
  mytable;
SET @sql = CONCAT('SELECT label, ', @sql, ' 
                  FROM mytable 
                   GROUP BY label');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Demo here

SELECT `label`, count(`sublabel`)
FROM `scraping_data`
WHERE `label` = 'application' -- < optional
GROUP BY `label`

Group by and Count.