[NBLUG/talk] SQL statement

Walter Hansen gandalf at sonic.net
Fri Aug 20 13:28:26 PDT 2004


I think your closest. The union works, but I need to combine hits and
clicks for each domain on one line. The idea of making a temporay combined
table is a good one and should work although I don't have time to try it
right now. But I can deffinately see how it would work.

In case anyone doesn't get this setup. Hits counts hits on a given domain
recording only the domain name and the time of the hit. Hits also has some
encoding on the domain name that helps track bots and non-top web pages
and other things. Clicks tracks clicks going out from a domain to another
site through a clickcounter. The simplistic tables are meant to speed up
the process by only having a couple of data items. I wanted the tracking
to impact the acutal end user as little as possible. Also this provides a
great deal of flexablity on the SQL side for examineing the data.

I think the temporay table solution is the best so far. And yes I'm
generally using count(*) to obtain the number of references to a
particular domain along with group by Domain. I just thought there might
be a way to do this with a subselect, but I don't see any ever used in the
select part of the query in any of the documentation I see.

>> 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
>
> _______________________________________________
> talk mailing list
> talk at nblug.org
> http://nblug.org/cgi-bin/mailman/listinfo/talk
>
>






More information about the talk mailing list