Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Imagine a MySQL table that only has 2 columns: an id and a name of a color.

With this query, I know how many id's I have for each color:

SELECT color_name, count(id) FROM color_table GROUP BY (color_name)

red:    10 
blue:   5 
yellow: 3
green:  1

My question is, is there a way I can specify to the "group by" some custom groups?? I mean, is there a query that results in this:

red: 10
colors different than red: 9
share|improve this question
add comment

4 Answers

Directly using GROUP BY, no. You can still get the results you want, though, and there are many ways to accomplish it. Such as UNION:

SELECT 'red', COUNT(*) FROM color_table WHERE color_name='red'
UNION
SELECT 'colors different than red', COUNT(*) FROM color_table WHERE color_name <> 'red'
share|improve this answer
    
Only other thing would be where NULLs should go (either not red or are they ignored)? –  Cade Roux Jun 6 '12 at 20:00
add comment

Although it looks a little sloppy, GROUP BY is possible

SELECT is_it_mycolor,COUNT(1) mycolor_count FROM
(SELECT IF(color_name='red','red','colors different than red') is_it_mycolor
FROM color_table) A GROUP BY is_it_mycolor order by mycolor_count desc;

Here is the sample data

drop database if exists alex;
create database alex;
use alex
create table color_table
(id int not null auto_increment,
color_name varchar(20),primary key (id));
insert into color_table (color_name) values
('yellow'),('blue'),('red'),('red'),('red'),
('blue'),('red'),('green'),('red'),('red'),
('yellow'),('blue'),('red'),('red'),('yellow'),
('blue'),('red'),('red'),('blue');
SELECT color_name, count(id) FROM color_table GROUP BY (color_name);

Here is the sample data loaded

mysql> drop database if exists alex;
Query OK, 1 row affected (0.03 sec)

mysql> create database alex;
Query OK, 1 row affected (0.00 sec)

mysql> use alex
Database changed
mysql> create table color_table
    -> (id int not null auto_increment,
    -> color_name varchar(20),primary key (id));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into color_table (color_name) values
    -> ('yellow'),('blue'),('red'),('red'),('red'),
    -> ('blue'),('red'),('green'),('red'),('red'),
    -> ('yellow'),('blue'),('red'),('red'),('yellow'),
    -> ('blue'),('red'),('red'),('blue');
Query OK, 19 rows affected (0.06 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> SELECT color_name, count(id) FROM color_table GROUP BY (color_name);
+------------+-----------+
| color_name | count(id) |
+------------+-----------+
| blue       |         5 |
| green      |         1 |
| red        |        10 |
| yellow     |         3 |
+------------+-----------+
4 rows in set (0.00 sec)

mysql>

Here is my query executed against the sample data

mysql> SELECT is_it_mycolor,COUNT(1) mycolor_count FROM
    -> (SELECT IF(color_name='red','red','colors different than red') is_it_mycolor
    -> FROM color_table) A GROUP BY is_it_mycolor order by mycolor_count desc;
+---------------------------+---------------+
| is_it_mycolor             | mycolor_count |
+---------------------------+---------------+
| red                       |            10 |
| colors different than red |             9 |
+---------------------------+---------------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!

share|improve this answer
add comment

For flexibility, you can use a color_group table, either permanent or temporary or on the fly:

SELECT color_group.custom_group
     , COUNT(c.id) AS cnt
FROM 
        ( SELECT 'red' AS color_name, 'red' AS custom_group UNION ALL
          SELECT 'green', 'other' UNION ALL
          SELECT 'blue' , 'other' UNION ALL
          ...
          SELECT 'black', 'other'
        ) AS color_group
    JOIN
        color_table AS c
      ON c.color_name = color_group.color_name
GROUP BY color_group.custom_group ;
share|improve this answer
add comment

Not sure about MYSQL but you can use a CASE in the Group BY with SQL Server.

SELECT  CASE color_name
      WHEN 'red' THEN 'red'
      ELSE 'colors different than red'
    END AS color_group
   ,Count(id)
FROM    color_table
GROUP BY CASE color_name
      WHEN 'red' THEN 'red'
      ELSE 'colors different than red'
    END
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.