Hi,
I'm trying to write an SQL statement that provides a list of insurance/
product groups and then lists a total and percentage on the same line
e.g.
XX 5 4 80%
XY 10 5 50%
I can get the first 3 columns done ok but can't do the percentage.
The totals are calculated based on subqueries and I've named the
subqueries and would expect to be able to use the value returned in
the percentage calculation except I get the error "Column ONCOVER not
in specified tables."
Can anybody help please? I've attached the SQL below
Thanks
Steve
select mrnpty "Scheme", mrncru "User", mrnsts "Status", mrnpap
"Paperless",
(select count(*) from elinew.mrnquote b
where mrncrd = curdate()
and b.mrnsts = 'G'
and a.mrnpol = b.mrnpol
and a.mrnpty = b.mrnpty
and a.mrncru = b.mrncru
group by mrncru, mrnpty, mrnsts, mrnpap) AS Enquiry ,
(select count(*) OncoverTotal from elinew.mrnquote b
where mrncrd = curdate()
and b.mrnsts = 'O'
and a.mrnpol = b.mrnpol
and a.mrnpty = b.mrnpty
and a.mrncru = b.mrncru
group by mrncru, mrnpty, mrnsts, mrnpap) AS OnCover,
((OnCover / Enquiry)*100)
from elinew.mrnquote a, sysibm.sysdummy1
where mrncrd = curdate()
union
select 'Total', (select count(*) from elinew.mrnquote where mrncrd =
curdate() and mrnsts IN ('O', 'G')),
'Paperless', (select count(*) from elinew.mrnquote where mrncrd =
curdate() and mrnsts IN ('O', 'G') and mrnpap = 'Y'), ' ', ' ', ' '
from sysibm.sysdummy1