title: MySQL Query Select from table count total and used, calculate remaining
body: <p>I have two tables :</p>
  
  <pre><code>-- **phones**
  
  CREATE TABLE IF NOT EXISTS `phones` (
  `phone_id` int(11) NOT NULL AUTO_INCREMENT,
  `phone_type_id` int(11) NOT NULL,
  `phone_mac` varchar(255) NOT NULL,
  `office_id` int(11) DEFAULT NULL,
  `did_id` int(11) DEFAULT NULL,
  `virtual_extension_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`phone_id`),
  UNIQUE KEY `phone_mac` (`phone_mac`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=67 ;
  
  INSERT INTO `phones` (`phone_id`, `phone_type_id`, `phone_mac`, `office_id`, `did_id`, `virtual_extension_id`) VALUES
  (1, 1, '0004f22ccccc', 5, NULL, NULL),
  (63, 1, '0004f22bbbbb', 9, 33, NULL),
  (64, 20, '0004f22aaaaa', 6, NULL, 6600),
  (65, 2, '000033434333', 9, NULL, NULL),
  (66, 20, '21232da32434', 6, NULL, NULL);
  
  --  **phone_types**
  
  CREATE TABLE IF NOT EXISTS `phone_types` (
    `phone_type_id` int(11) NOT NULL AUTO_INCREMENT,
    `phone_type_manufacturer` varchar(255) NOT NULL,
    `phone_type_model` varchar(255) NOT NULL,
    PRIMARY KEY (`phone_type_id`),
    UNIQUE KEY `phone_type_model` (`phone_type_model`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;
  
  INSERT INTO `phone_types` (`phone_type_id`, `phone_type_manufacturer`, `phone_type_model`) VALUES
  (1, 'Polycom', 'SoundPoint IP 550'),
  (2, 'Polycom', 'SoundPoint IP 450'),
  (3, 'Polycom', 'SoundPoint IP 335'),
  (4, 'Polycom', 'SoundPoint IP 560'),
  (5, 'Polycom', 'SoundPoint IP 670'),
  (6, 'Polycom', 'SoundStation IP 5000'),
  (7, 'Polycom', 'SoundStation IP 6000'),
  (8, 'Yealink', 'W52P'),
  (20, 'Aastra', '6757i CT');
  </code></pre>
  
  <p>I am able to select all the phones from the phone_types table and then get a total count of how many of those phones are within the Phones table, phone_type_id column. </p>
  
  <p>I need to count how many of those phones have a value (NOT NULL) in the Phones table, in did_id or virtual_extension_id. This is where I'm hung up. </p>
  
  <p>Here's what I have so far. Note that I currently am using the same count total and used as I can't figure out the used count. Any help is greatly appreciated.
  Thanks in advance.</p>
  
  <pre><code>SELECT 
  phone_types.phone_type_id AS phone_type_id, 
  phone_type_manufacturer AS phone_type_manufacturer, 
  phone_type_model AS phone_type_model, 
  COUNT( phones.phone_type_id ) AS total_count, 
  COUNT( phones.phone_type_id ) AS used_count
  FROM  phone_types 
  LEFT JOIN phones ON phones.phone_type_id = phone_types.phone_type_id
  GROUP BY phone_types.phone_type_id
  </code></pre>
  
  <p>Sample Output:</p>
  
  <pre><code>    1 | Polycom | SoundPoint IP 550    | 2 | 2
      2 | Polycom | SoundPoint IP 450    | 0 | 0
      3 | Polycom | SoundPoint IP 335    | 0 | 0
      4 | Polycom | SoundPoint IP 560    | 0 | 0
      5 | Polycom | SoundPoint IP 670    | 0 | 0
      6 | Polycom | SoundStation IP 5000 | 0 | 0
      7 | Polycom | SoundStation IP 6000 | 0 | 0
      8 | Yealink | W52P                 | 0 | 0
     20 | Aastra  | 6757i CT             | 1 | 1
  </code></pre>
  
  <p>Edited: added create and insert statements.</p>
  
  <p>Edited: Seem to be making progress.</p>
  
  <p>SELECT phone_types.phone_type_manufacturer, phone_types.phone_type_model, COUNT( phones.phone_type_id ) AS Total, COUNT( phones.did_id
  OR virtual_extension_id ) AS Used
  FROM  <code>phones</code> 
  INNER JOIN phone_types ON phones.phone_type_id = phone_types.phone_type_id
  GROUP BY phones.phone_type_id</p>
  
  <p>which gives me the following output:
  manufacturer  model              total  Used
  Polycom       Soundpoint IP 550  2      1
  Polycom       SoundPoint IP 450  1      0
  Aastra        6757i CT           2      1</p>
  
  <p>This is correct but I need to show the other phones also that have a NULL value in both did_id and virtual_extension_id?</p>