select q.*, (select count(*) from lang_messages lm1 left outer join lang_messages lm2 on (lm2.locale = :current_locale and lm2.message_key = lm1.message_key and lm2.package_key = lm1.package_key), lang_message_keys lmk where lm1.locale = :default_locale and lm1.package_key = q.package_key and lm1.message_key = lmk.message_key and lm1.package_key = lmk.package_key and lm2.message is not null and lm1.deleted_p = 'f' and lm2.deleted_p = 'f' ) as num_translated, (select count(*) from lang_messages lm1 left outer join lang_messages lm2 on (lm2.locale = :current_locale and lm2.message_key = lm1.message_key and lm2.package_key = lm1.package_key), lang_message_keys lmk where lm1.locale = :default_locale and lm1.package_key = q.package_key and lm1.message_key = lmk.message_key and lm1.package_key = lmk.package_key and lm2.message is null and lm1.deleted_p = 'f' and (lm2.deleted_p = 'f' or lm2.deleted_p is null) ) as num_untranslated, (select count(*) from lang_messages lm1 left outer join lang_messages lm2 on (lm2.locale = :current_locale and lm2.message_key = lm1.message_key and lm2.package_key = lm1.package_key), lang_message_keys lmk where lm1.locale = :default_locale and lm1.package_key = q.package_key and lm1.message_key = lmk.message_key and lm1.package_key = lmk.package_key and (lm1.deleted_p = 't' or lm2.deleted_p = 't') ) as num_deleted from (select package_key, count(message_key) as num_messages from lang_messages where locale = :default_locale group by package_key ) q order by package_key select q.*, (select count(*) from lang_messages where package_key = q.package_key and locale = :default_locale and message is not null and deleted_p = 'f' ) as num_translated, (select count(*) from lang_messages where package_key = q.package_key and locale = :default_locale and message is null and deleted_p = 'f' ) as num_untranslated, (select count(*) from lang_messages where package_key = q.package_key and locale = :default_locale and deleted_p = 't' ) as num_deleted from (select package_key, count(message_key) as num_messages from lang_messages where locale = :default_locale group by package_key ) q order by package_key