How to subtract derived columns

[Shanondink]

Hi,

I need NUMBER_TO_ORDER to contain the difference between DEVICE_COUNT and TARGET.

But I just can't seem to figure out to to get them to subtract.

[tt]
mysql> SELECT
-> product_type as 'MODEL',
-> REPLACE(REPLACE(REPLACE(available, 900, 1),1, 'New'),2, 'Used') AS 'CONDITION',
-> count(*) as 'DEVICE_COUNT',
-> (SELECT `value` FROM `SS_misc` WHERE `customer_number` = 169 AND `setting` = CONCAT(MODEL,'_',`CONDITION`,'_targetqty')) AS 'TARGET',
-> 'TARGET' - 'DEVICE_COUNT' AS 'NUMBER_TO_ORDER'
-> FROM SS_serial_numbers
-> WHERE
-> customer_number LIKE '10069' AND
-> (available > 0 AND available <= 900) AND
-> available != 4
-> GROUP BY MODEL, `CONDITION`, TARGET
-> ORDER BY `CONDITION`, MODEL
-> ;
+------------------+-----------+--------------+--------+-----------------+
| MODEL | CONDITION | DEVICE_COUNT | TARGET | NUMBER_TO_ORDER |
+------------------+-----------+--------------+--------+-----------------+
| PAP2T-NA,3691 | New | 108 | 120 | 0 |
| SPA-1001,1853 | New | 8 | 0 | 0 |
| SPA-2102-NA,1854 | New | 99 | 160 | 0 |
| SPA-921,1856 | New | 231 | 120 | 0 |
| SPA-922,3677 | New | 24 | 230 | 0 |
| SPA-941,3598 | New | 42 | 60 | 0 |
| SPA-942,3664 | New | 84 | 40 | 0 |
| SPA-962,2332 | New | 10 | 10 | 0 |
| SPA-2102-NA,1854 | Used | 7 | NULL | 0 |
| SPA-921,1856 | Used | 32 | NULL | 0 |
| SPA-941,3598 | Used | 2 | NULL | 0 |
+------------------+-----------+--------------+--------+-----------------+
11 rows in set, 22 warnings (0.58 sec)
[/tt]

Thanks in advance,
Shanon

 

[r937]

SELECT `MODEL`
     , `CONDITION`
     , `DEVICE_COUNT`
     , `TARGET`
     , `TARGET` - `DEVICE_COUNT` AS NUMBER_TO_ORDER
  FROM (
       SELECT product_type as 'MODEL'
            , REPLACE(REPLACE(REPLACE(available, 900, 1)
                     ,1, 'New'),2, 'Used') AS 'CONDITION'
            , count(*) as 'DEVICE_COUNT'
            , ( SELECT `value` 
                  FROM `SS_misc` 
                 WHERE `customer_number` = 169 
                   AND `setting` = CONCAT(MODEL
                                     ,'_',`CONDITION`
                                     ,'_targetqty')
                     ) AS 'TARGET'
         FROM SS_serial_numbers
        WHERE customer_number LIKE '10069' 
          AND (available > 0 AND available <= 900) 
          AND available != 4
       GROUP 
           BY MODEL
            , `CONDITION`
            , TARGET
       ) AS d
ORDER 
    BY `CONDITION`
     , `MODEL`

r937.com | rudy.ca

 

[Shanondink]

Thanks Rudy!

That helped a bunch.

Shanon