skip to Main Content

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

2

Answers


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

    Group by and Count.

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search