SELECT DISTINCT ttl.tpid, (SELECT title FROM tariff_plan WHERE id=ttl.tpid) AS title,
(SELECT SUM(amount)/1073741824.0 FROM session_detail_1_201001 WHERE sid=4 AND session_id IN (
   SELECT id FROM log_session_1_201001 WHERE lid IN (
       SELECT id FROM user_login_1 WHERE cid IN(
          SELECT cid FROM contract_tariff WHERE tpid=ttl.tpid AND (date1 IS NULL OR date1<='2010-01-15') AND (date2 IS NULL or date2 > '2009-10-01')
         )
      )
   )
) AS trafik
FROM tariff_tree_link ttl LEFT JOIN module_tariff_tree mtt ON ttl.tree_id=mtt.tree_id
WHERE mtt.mid=1 HAVING NOT title IS NULL

SELECT DISTINCT ttl.tpid, (SELECT title FROM tariff_plan WHERE id=ttl.tpid) AS title,
(SELECT SUM(amount)/1073741824.0 FROM session_detail_1_201001 WHERE sid=2 AND dtime=2010-01-01 AND session_id IN (
   SELECT id FROM log_session_1_201001 WHERE lid IN (
       SELECT id FROM user_login_1 WHERE cid IN(
          SELECT cid FROM contract_tariff WHERE tpid=ttl.tpid AND (date1 IS NULL OR date1<='2010-01-15') AND (date2 IS NULL or date2 > '2009-10-01')
         )
      )
   )
) AS trafik
FROM tariff_tree_link ttl LEFT JOIN module_tariff_tree mtt ON ttl.tree_id=mtt.tree_id
WHERE mtt.mid=1 HAVING NOT title IS NULL

SELECT DISTINCT ttl.tpid, (SELECT title FROM tariff_plan WHERE id=ttl.tpid) AS title,
(SELECT SUM(amount)/28800 FROM session_detail_1_201001 WHERE sid=2 AND dtime BETWEEN '2010-01-01 15:00:00' AND '2010-01-01 16:00:00' AND session_id IN (
   SELECT id FROM log_session_1_201001 WHERE lid IN (
       SELECT id FROM user_login_1 WHERE cid IN(
          SELECT cid FROM contract_tariff WHERE tpid=ttl.tpid AND (date1 IS NULL OR date1<='2010-01-15') AND (date2 IS NULL or date2 > '2009-10-01')
         )
      )
   )
) AS trafik
FROM tariff_tree_link ttl LEFT JOIN module_tariff_tree mtt ON ttl.tree_id=mtt.tree_id
WHERE mtt.mid=1 HAVING NOT title IS NULL

SELECT DISTINCT ttl.tpid, (SELECT title FROM tariff_plan WHERE id=ttl.tpid) AS title,
(SELECT SUM(amount)/1073741824.0 FROM session_detail_1_201001 WHERE sid=4 AND cid IN (
          SELECT cid FROM contract_tariff WHERE tpid=ttl.tpid AND (date1 IS NULL OR date1<='2010-01-15') AND (date2 IS NULL or date2 > '2009-10-01')
   )
) AS trafik
FROM tariff_tree_link ttl LEFT JOIN module_tariff_tree mtt ON ttl.tree_id=mtt.tree_id
WHERE mtt.mid=1 HAVING NOT title IS NULL

SELECT DISTINCT (select COUNT(id) from contract) AS contract, (SELECT title FROM tariff_plan WHERE id=ttl.tpid) AS title,
(SELECT SUM(amount)/1073741824.0 FROM session_detail_1_201001 WHERE sid=2 AND cid IN (
          SELECT cid FROM contract_tariff WHERE tpid=ttl.tpid AND (date1 IS NULL OR date1<='2010-01-15') AND (date2 IS NULL or date2 > '2009-10-01')
   )
) AS trafik
FROM tariff_tree_link ttl LEFT JOIN module_tariff_tree mtt ON ttl.tree_id=mtt.tree_id
WHERE mtt.mid=1 HAVING NOT title IS NULL

select DISTINCT tariff_plan.title, (select DISTINCT count(tpid) from contract_tariff where contract_tariff.tpid=tariff_plan.id)as contact, (select sum(amount) from session_detail_1_201002 where contract_tariff.cid=session_detail_1_201002.cid and sid=2)as trafic from session_detail_1_201002, contract_tariff, tariff_plan where contract_tariff.cid=session_detail_1_201002.cid and contract_tariff.tpid=tariff_plan.id;

(select DISTINCT count(tpid) from contract_tariff where contract_tariff.tpid=tariff_plan.id)as contact