sql subquery problem

Question

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 like this

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

Can you help me about this problem.

Answer 1

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ış');