Mysql Question posted in
The official MySQL documentation can be found here.

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

Tags:

2

Answers


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

    Group by and Count.

    Login or Signup to reply.
  2. 0

    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