VIEW: APPS.IMC_STATE_PER_RPT_V

Source

View Text - Preformatted

SELECT nvl(a.state,'Undefined') State, a.customers, to_char(round(a.customers*100/b.tot,2)) percent, a.country from (select state, country, count(*) customers from hz_parties where party_type='PERSON' group by country, state) a, (select country, count(*) tot from hz_parties where party_type='PERSON' group by country) b WHERE a.country = b.country union select 'TOTAL', count(*), '100.00', country from hz_parties where country is not null and party_type='PERSON' group by country order by 2 asc

View Text - HTML Formatted

SELECT NVL(A.STATE
, 'UNDEFINED') STATE
, A.CUSTOMERS
, TO_CHAR(ROUND(A.CUSTOMERS*100/B.TOT
, 2)) PERCENT
, A.COUNTRY
FROM (SELECT STATE
, COUNTRY
, COUNT(*) CUSTOMERS
FROM HZ_PARTIES
WHERE PARTY_TYPE='PERSON' GROUP BY COUNTRY
, STATE) A
, (SELECT COUNTRY
, COUNT(*) TOT
FROM HZ_PARTIES
WHERE PARTY_TYPE='PERSON' GROUP BY COUNTRY) B
WHERE A.COUNTRY = B.COUNTRY UNION SELECT 'TOTAL'
, COUNT(*)
, '100.00'
, COUNTRY
FROM HZ_PARTIES
WHERE COUNTRY IS NOT NULL
AND PARTY_TYPE='PERSON' GROUP BY COUNTRY ORDER BY 2 ASC