CODEWITHSUNDEEP
mysqlsql
Pi Horse
Pi Horse

Reputation: 2430

MySQL Query - Grouping Values and Aggregrating

I fire the following query to my table : 

SELECT code_ver, 
       platform_type, 
       category_name 
FROM   builds 
WHERE  runtype LIKE 'test' 
       AND platform_type NOT LIKE 'total'; 

+-----------+---------------+---------------+
| code_ver  | platform_type | category_name |
+-----------+---------------+---------------+
| 10.0.1.50 | 8k            | UMTS          | 
| 10.0.1.50 | 8k            | UMTS          | 
| 10.0.1.50 | 8k            | IP            | 
| 10.0.1.50 | 8k            | IP            | 
| 10.0.1.50 | 9k            | IP            | 
| 10.0.1.50 | 9k            | IP            | 
| 10.0.1.50 | 9k            | UMTS          | 
| 10.0.1.50 | 9k            | UMTS          | 
| 10.0.1.50 | 9k            | UMTS          | 
| 10.0.1.50 | 9k            | Stability     | 
| 10.0.1.50 | 9k            | Stability     | 
| 10.0.1.51 | 8k            | UMTS          | 
| 10.0.1.51 | 8k            | UMTS          | 
| 10.0.1.51 | 8k            | IP            | 
| 10.0.1.51 | 8k            | IP            | 
| 11.0.1.50 | 9k            | UMTS          | 
| 11.0.1.50 | 9k            | IP            | 
+-----------+---------------+---------------+

I fire the following query 

SELECT code_ver, 
       platform_type, 
       Count(*) 
FROM   builds 
WHERE  runtype LIKE 'test' 
       AND platform_type NOT LIKE 'total' 
GROUP  BY code_ver, 
          platform_type; 

+-----------+---------------+----------+
| code_ver  | platform_type | count(*) |
+-----------+---------------+----------+
| 10.0.1.50 | 8k            |        4 | 
| 10.0.1.50 | 9k            |        7 | 
| 10.0.1.51 | 8k            |        4 | 
| 11.0.1.50 | 9k            |        2 | 
+-----------+---------------+----------+

And when I fire the following query 

SELECT code_ver, 
       platform_type, 
       Count(*) 
FROM   builds 
WHERE  runtype LIKE 'test' 
       AND platform_type NOT LIKE 'total' 
GROUP  BY code_ver, 
          platform_type,
          category_name; 

+-----------+---------------+----------+
| code_ver  | platform_type | count(*) |
+-----------+---------------+----------+
| 10.0.1.50 | 8k            |        2 | 
| 10.0.1.50 | 8k            |        2 | 
| 10.0.1.50 | 9k            |        2 | 
| 10.0.1.50 | 9k            |        2 | 
| 10.0.1.50 | 9k            |        3 | 
| 10.0.1.51 | 8k            |        2 | 
| 10.0.1.51 | 8k            |        2 | 
| 11.0.1.50 | 9k            |        1 | 
| 11.0.1.50 | 9k            |        1 | 
+-----------+---------------+----------+

But I want the count of unique combinations of (code_ver + platform_type + category_name) such that the output should look like this :

+-----------+---------------+----------+
| code_ver  | platform_type | count(*) |
+-----------+---------------+----------+
| 10.0.1.50 | 8k            |        2 | 
| 10.0.1.50 | 9k            |        3 | 
| 10.0.1.51 | 8k            |        2 | 
| 11.0.1.50 | 9k            |        2 | 
+-----------+---------------+----------+

Can anybody give me some suggestion here.

Upvotes: 1

Views: 79

Answers (2)

Ravi K Thapliyal
Ravi K Thapliyal

Reputation: 51711

Use a SQL inner query.

SELECT ta.code_ver, 
   ta.platform_type, 
   Count(*) 
FROM (SELECT code_ver, platform_type
      FROM   builds 
      WHERE  runtype LIKE 'test' 
          AND platform_type NOT LIKE 'total'
      GROUP  BY code_ver, platform_type, category_name) AS ta -- table alias
GROUP BY ta.code_ver, ta.platform_type;

Upvotes: 3

What have you tried
What have you tried

Reputation: 11138

Based on the result set you provided, it looks like you want to also group by category_name

    SELECT code_ver, 
           platform_type,
           Count(*) ,
           category_name
    FROM   builds 
    WHERE  runtype LIKE 'test' 
           AND platform_type NOT LIKE 'total' 
    GROUP  BY code_ver, 
              platform_type,
              category_name;

Upvotes: 0

Related Questions