[SOLVED] Counts/Group By on same table

[xtopolis]

Hi,

 

I have tried googling this with the keywords "inner join", but I think I am mistaken in what I need to use.  I'm using MySQL 5.0.  My table looks like this:

[pre]char_name      user_id    class_id    job_id    gender    height    region

bob        8          0          0        0        0        0

jer        44        0          2        0        0        0

dave        6          0          0        0        0        1

brian      17        7          5        1        1        0

steve      118        0          0        0        0        0

frank      92        3          3        1        0        1[/pre]

 

The result I want would group based on class_id and have 2 columns showing the region count.  So if two people with class_id = 0 have different region values, it would show:

class_id    region0  region1

0            1          1

 

 

What I want based on my data:[pre]class_id    region0    region1

0            3          1

7            1          0

3            0          1

[/pre]

 

Can anyone help point me in the right direction for this?

 

 

 

My table:

CREATE TABLE `accounts` (
  `char_name` varchar(255) collate latin1_german2_ci NOT NULL,
  `user_id` tinyint(5) unsigned NOT NULL,
  `class_id` tinyint(1) unsigned NOT NULL,
  `job_id` tinyint(1) unsigned NOT NULL,
  `gender` tinyint(1) unsigned NOT NULL,
  `height` tinyint(1) unsigned NOT NULL,
  `region` tinyint(1) unsigned NOT NULL,
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

-- 
-- Dumping data for table `accounts`
-- 

INSERT INTO `accounts` VALUES ('bob', 8, 0, 0, 0, 0, 0);
INSERT INTO `accounts` VALUES ('jer', 44, 0, 2, 0, 0, 0);
INSERT INTO `accounts` VALUES ('dave', 6, 0, 0, 0, 0, 1);
INSERT INTO `accounts` VALUES ('brian', 17, 7, 5, 1, 1, 0);
INSERT INTO `accounts` VALUES ('steve', 118, 0, 0, 0, 0, 0);
INSERT INTO `accounts` VALUES ('frank', 92, 3, 3, 1, 0, 1);

[xtopolis]

I already had this question solved in another post.  Oops.

[fenway]

Which one? Link to it.