[SOLVED] How to get this result set?

[xtopolis]

Hi,

 

One of my tables looks like this:

[pre]account_id  toon_name  user_id  class_id  craft_id  timezone

------- -----------------------------------------------------------

1            Steve      1        6          6          0

3            Barry      3        2          3          1

11          Fred        11        5          2          0

15          Lisa        15        1          3          1

20          Joe        20        6          5          1

32          Frank      32        1          0          0[/pre]

 

This query returns:

SELECT aa.class_id, COUNT(aa.class_id)
             FROM aion_accounts aa
             GROUP BY aa.class_id
             ORDER BY aa.class_id ASC

correctly returns this result:

class_id   COUNT( aa . class_id )
0          1
1          8
2          5
3          9
4          4
5          3
6          9
7          3
8          6

 

Question

I recently added the timezone column which is a tinyint that will hold values: NULL, 0, or 1  (perhaps more, but not likely).  Is there a way get a result set which has the query above + two additional columns: a count for timezone set to 0, and timezone set to 1 ?

 

So it would be like this (based on that table):

class_id   COUNT( aa . class_id )   COUNT(timezone=0)   COUNT(timezone=1)
0          1                        0                   0
1          8                        1                   0
2          5                        0                   0
3          9                        0                   1
4          4                        0                   0
5          3                        1                   0
6          9                        1                   1
7          3                        0                   0
8          6                        0                   0

How would I write this query?  I have a feeling is has to do with a sub query or self join, but I am not well practiced in those areas.  Any tips would be appreciated.

(Also, if you think my table should be normalized differently (put timezone elsewhere), I am open to suggestions.

 

.

.

.

.

Source:

CREATE TABLE `aion_accounts` (
  `account_id` int(5) NOT NULL auto_increment,
  `toon_name` varchar(255) collate latin1_german2_ci NOT NULL,
  `user_id` int(5) NOT NULL,
  `class_id` tinyint(1) NOT NULL,
  `craft_id` tinyint(1) NOT NULL,
  `timezone` tinyint(1) default NULL,
  PRIMARY KEY  (`account_id`)
) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=50 ;

INSERT INTO `aion_accounts` VALUES (1, 'Steve', 1, 6, 6, 0);
INSERT INTO `aion_accounts` VALUES (3, 'Barry', 3, 2, 3, 1);
INSERT INTO `aion_accounts` VALUES (11, 'Fred', 11, 5, 2, 0);
INSERT INTO `aion_accounts` VALUES (15, 'Lisa', 15, 1, 3, 1);
INSERT INTO `aion_accounts` VALUES (20, 'Joe', 20, 6, 5, 1);
INSERT INTO `aion_accounts` VALUES (32, 'Frank', 32, 1, 0, 0);

[Daniel0]

Something like this?

 

SELECT
    main.class_id,
    COUNT(main.class_id) AS class_id,
    (
       SELECT COUNT(*)
       FROM aion_accounts AS tz0
       WHERE tz0.timezone = 0
         AND tz0.class_id = main.class_id
    ) AS tz0,
    (
       SELECT COUNT(*)
       FROM aion_accounts AS tz1
       WHERE tz1.timezone = 1
         AND tz1.class_id = main.class_id
    ) AS tz1
  FROM aion_accounts AS main
  GROUP BY main.class_id;

[xtopolis]

Thanks, while that version produced this error:

SQL query:

SHOW KEYS FROM `main`

 

MySQL said:

#1146 - Table 'db290584181.main' doesn't exist

 

I was able to fix it by using removing the alias for the main table:

SELECT
    aion_accounts.class_id,
    COUNT(aion_accounts.class_id) as class_id,
    (
        SELECT COUNT(timezone)
            FROM aion_accounts tz0
            WHERE tz0.timezone = 0
                AND tz0.class_id = aion_accounts.class_id
    ) AS US,
    (
        SELECT COUNT(timezone)
            FROM aion_accounts tz1
            WHERE tz1.timezone = 1
                AND tz1.class_id = aion_accounts.class_id
    ) AS EU
    
    FROM aion_accounts
    GROUP BY aion_accounts.class_id

 

If possible, could you explain the above error?  I tried googling on "SHOW KEYS FROM", but didn't see any solutions/explanations.  Also, using these ?subqueries?, is this an efficient way of doing this?  Would it be better to join another table instead on a FK of aion_accounts?  I define better as which would be less taxing on the query itself.

[fenway]

The error was simply the fact that the outer table alias isn't visible to the subqueries.