[NBLUG/talk] SQL statement

[Rich Gibson]

> Is this what you want?
> 
> select hits.domain,clicks.count as clicks,hits.count as hits
> from hits,clicks
> where hits.domain = clicks.domain;

There is no count attribute/column name/field name in either table...is 
there a magic 'count' autovivification feature in mysql 4?  

To get the individual counts in two queries:

select hits.domain, count(hits.domain)
from hits
group by domain

And for clicks...

select clicks.domain, count(clicks.domain)
from clicks
group by domain

In MySQL 4 you can use the Union statement to create the 'union' of those 
two queries.

something like this will probably be on the right path, but I can't test 
it right now.

select clicks.domain, count(clicks.domain) as click_cnt, 0 as hit_cnt                                         
from clicks                                                                        
group by domain 
union
select hits.domain, 0 as click_cnt, count(hits.domain) as hit_cnt                                        
from hits                                                                        
group by domain 


Other options exist...

Personally I would have a different schema (there are many many ways to do 
SQL, this is just one).

create table click_hit 
  (domain varchar(255), type char(1), stamp datetime);

(or use an enum in place of the 'type' field) 
type enum ('C', 'H')

Then load this table from your existing  tables:
insert into click_hit select domain, 'C', t from clicks;
insert into click_hit select domain, 'H', t from hits;

('t' is my timestamp field)

And query it with one of my favorite tricks of using sum() to create 
crosstab queries:

select domain, 
sum(if(type='C', 1, 0)) as click_cnt, 
sum(if(type='H',1,0)) 
as hit_count from click_hit group by domain;

(this is creating a count.  if we had a numeric field we could use it to 
get totals).

for example, if we had the field price this would give us total price...
sum(if(type='C',price,0)) as c_price

I think that is just cool.

But the union is w