sql subquery problem

Im little speak english.

I have an sql subquery error

Database : MySQL

Table type : MyISAM

the following my sql query

SELECT
(SELECT sum(`total`) FROM `staff_history` WHERE `type` = 'Giriş' AND staff_id = table_1.staff_id) AS `input`,
(SELECT sum(`total`) FROM `staff_history` WHERE `type` = 'Çıkış' AND staff_id = table_1.staff_id) AS `output`,
(`input` - `output`) AS `balance`
FROM `staff_history` AS `table_1` WHERE `staff_id` = '2';

I get an error开发者_StackOverflow like this

Error code 1054, SQL status 42S22: Unknown column 'input' in 'field list'

Can you help me about this problem.

You cannot use the fieldnames there already, because they are not available in this scope.

You could duplicate the whole expression

SELECT
(SELECT sum(`total`) FROM `staff_history` 
WHERE `type` = 'Giriş' AND staff_id = table_1.staff_id) AS `input`,
(SELECT sum(`total`) FROM `staff_history` 
WHERE `type` = 'Çıkış' AND staff_id = table_1.staff_id) AS `output`,
(SELECT sum(`total`) FROM `staff_history` 
WHERE `type` = 'Giriş' AND staff_id = table_1.staff_id) 
-
(SELECT sum(`total`) FROM `staff_history` 
WHERE `type` = 'Çıkış' AND staff_id = table_1.staff_id) AS `balance`
FROM `staff_history` AS `table_1` WHERE `staff_id` = '2';

The query optimizer handles this remarkable well, but it is not very maintanable, so you could also put the entire query in a subquery:

SELECT
  x.`input`,
  x.`output`,
  x.`input` - x.`output` as `balance`
FROM
  (SELECT
    (SELECT sum(`total`) 
    FROM `staff_history` 
    WHERE `type` = 'Giriş' AND staff_id =  table_1.staff_id) AS `input`,
    (SELECT sum(`total`) 
    FROM `staff_history` 
    WHERE `type` = 'Çıkış' AND staff_id = table_1.staff_id) AS `output`
  FROM 
    `staff_history` AS `table_1` 
  WHERE `staff_id` = '2') x;

I offer 1 SQL statement, 1 table scan:

select sum(case when type = 'Giriş' then total else 0 end) as input
      ,sum(case when type = 'Çıkış' then total else 0 end) as output
      ,sum(case when type = 'Giriş' then total else 0 end) - 
       sum(case when type = 'Çıkış' then total else 0 end) as balance
  from staff_history
 where staff_id = 2
   and type in('Giriş', 'Çıkış');