rewrite count distinct query - Mailing list pgsql-general

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.