Hello I am running the below union query which is giving me the correct
results of count of hits by country.
The query draws data from 2 databases which is not ideal but is the way
it is. This is why it is a union query.
The results are great but in some instances a country will appear twice,
with two different count values, I want the country to appear once with
the total count of both results.
For instance this is some results
Country count
Hungary 2
Hungary 10
Czech Rep 9
Czech Rep 13
This is what I want
Hungary 12
Czech Rep 22
Here is the query. I would like to do it all in the Q with my limited
knowledge all I can think of is doing some post processing which is not
ideal.
I am running ORACLE 9i
Thanks for reading.
Cheers
Martin
<cfquery name="countByCountry"
datasource="#variables.dsn#">
select cl.COU_LIB "COUNTRY", count(p.rowid) "NB_PROFILES"
from intermediary i, profile p, contact_intermediary
ci, country_lib cl
where i.INT_CODE = ci.INT_CODE
and p.CIN_ID = ci.CIN_ID(+)
and (cl.COU_ID = i.cou_id and cl.COU_LANG = 1)
and p.PRO_CREATION_DATE >= to_date
('#StartDate#','dd/mm/yyyy')
and p.PRO_CREATION_DATE < (to_date
('#EndDate#','dd/mm/yyyy') + 1)
group by cl.COU_LIB
union all
select newgide.country_iso.cis_lib AS COUNTRY, count(p.rowid)
"NB_PROFILES"
from profile p,
newgide.pers_eic, newgide.eic,
newgide.country_iso
where p.cin_pers_id = newgide.pers_eic.per_id(+)
and newgide.pers_eic.eic_code =
newgide.eic.eic_code(+)
and newgide.eic.pay_code =
newgide.country_iso.cis_code(+)
and newgide.country_iso.cis_lang = 'EN'
and p.PRO_CREATION_DATE >= to_date
('#startDate#','dd/mm/yyyy')
and p.PRO_CREATION_DATE < (to_date
('#endDate#','dd/mm/yyyy') + 1)
group by newgide.country_iso.cis_lib
order by COUNTRY
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246501
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
