rewrite count distinct query

Hi all,

'Scuse the long post :) I'm trying to include all relevant info..

I'm trying to work out a better way to approach a query, any tips are
greatly appreciated.

The relevant tables:

db=# \d tp_conversions
Table "public.tp_conversions"
Column | Type | Modifiers
---------------+------------------------+--------------------
conversionid | integer | not null default 0
type | character varying(10) |
name | character varying(255) |
amount | double precision |
cookieid | character varying(32) |
currtime | integer |
ip | character varying(20) |
origintype | character varying(20) |
originfrom | character varying(255) |
origindetails | character varying(255) |
userid | integer |
Indexes:
"tp_conversions_pkey" primary key, btree (conversionid)
"conv_origindetails" btree (origindetails)
"conv_originfrom" btree (originfrom)
"conv_origintype" btree (origintype)
"conv_time" btree (currtime)
"conv_userid" btree (userid)

trackpoint=# SELECT count(*) from tp_conversions;
count
-------
261
(1 row)

db=# \d tp_search
Table "public.tp_search"
Column | Type | Modifiers
------------------+------------------------+--------------------
searchid | integer | not null default 0
searchenginename | character varying(255) |
keywords | character varying(255) |
currtime | integer |
ip | character varying(20) |
landingpage | character varying(255) |
cookieid | character varying(32) |
userid | integer |
Indexes:
"tp_search_pkey" primary key, btree (searchid)
"search_cookieid" btree (cookieid)
"search_keywords" btree (keywords)
"search_searchenginename" btree (searchenginename)
"search_userid" btree (userid)

trackpoint=# SELECT count(*) from tp_search;
count
-------
5086
(1 row)

What I'm trying to do...

Work out the number of conversions for each search origin.

This query works:

select
count(distinct conversionid) as convcount,
count(distinct searchid) as searchcount,
(count(distinct conversionid) / count(distinct searchid)) as perc,
s.searchenginename
from tp_conversions c, tp_search s
where
c.origintype='search' and s.searchenginename=c.originfrom and
s.userid=c.userid and c.userid=1
group by searchenginename
order by convcount desc;

convcount | searchcount | perc | searchenginename
-----------+-------------+------+------------------
15 | 2884 | 0 | Google
1 | 110 | 0 | Google AU
2 | 308 | 0 | Google CA
1 | 25 | 0 | Google CL
1 | 143 | 0 | Google DE
1 | 117 | 0 | Google IN
1 | 26 | 0 | Google NZ
3 | 49 | 0 | Google RO
1 | 60 | 0 | Google TH
2 | 174 | 0 | Yahoo
(10 rows)

However the percentage is wrong.

I can cast one to a float:

(count(distinct conversionid) / count(distinct searchid)::float)

and it'll give me a better percentage:

convcount | searchcount | perc | searchenginename
-----------+-------------+---------------------+------------------
15 | 2884 | 0.00520110957004161 | Google
3 | 49 | 0.0612244897959184 | Google RO
2 | 308 | 0.00649350649350649 | Google CA
2 | 174 | 0.0114942528735632 | Yahoo
1 | 110 | 0.00909090909090909 | Google AU
1 | 25 | 0.04 | Google CL
1 | 143 | 0.00699300699300699 | Google DE
1 | 117 | 0.00854700854700855 | Google IN
1 | 26 | 0.0384615384615385 | Google NZ
1 | 60 | 0.0166666666666667 | Google TH
(10 rows)

(I think the answer to this is 'no' but I'm going to ask anyway :P)
Is there an easier way to get the more-detailed percentage (it's meant
to work in multiple databases - so casting to a float won't work for
other db's) ?

More importantly... Is there a better way to write the query (I don't
like the count(distinct...) but it works and gives the right info) ?

I tried to do it with a union:

SELECT
count(searchid),
searchenginename
from tp_search s
where userid=1
group by searchenginename
union
select
count(conversionid),
originfrom
from tp_conversions c
where c.userid=1
group by originfrom;

but then realised that getting the data out with php would be a
nightmare (plus I can't get the percentages).

Lastly:

db=# SELECT version();
version
---------------------------------------------------------------
PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

(I know it's a little out of date, upgrading later this week).

Any suggestions/hints/tips welcome :)

Thanks,
Chris.