I have database squid with table log, which is filled up with log reports
from squid proxy server.
I have two problems.
The first one: I create select sql command and create view sql command and
in a both cases I got different results. Why? What I did wrong? Both sql
commands are show next down. This example maybe seems to be unusefull, but
it is only simple example. I wanted to create a table with colummns pcname,
BYTEs_sum (sum of bytes downloaded by one client PC) and sum of all PC's or
percentual relationship between bytes downloaded by one client and whole sum
of bytes.
> select pcname, sum(asize) as bytu_celkem from log group by pcname;
>
>
> pcname | BYTEs_sum
> ------------------------------+-----------
> 192.168.0.105 | 21545717
> 192.168.0.116 | 468
> 192.168.0.120 | 584
> 192.168.0.126 | 20408
> 192.168.0.128 | 103490
> 192.168.0.139 | 729633
> 192.168.0.141 | 0
> 192.168.0.151 | 676
> 192.168.0.164 | 74838
> 192.168.0.222 | 11409879
>
>
> create view v1 as select pcname, sum(asize) as bytu_celkem from log group
> by pcname
> select * from v1;
>
> pcname|bytu_celkem
> ------+-----------
> (0 rows)
>
The second one:
I tryed to group results by an URL. In the first example I used agregate
function count and in the second sum. The results of first example seems to
be not group by URL. Why? In all examples I used the same table.
I'am using Postgres version 6.2.1.
> select short_url, count(short_url) from log group by short_url order by 2
> asc;
>
> short_url |count
> ------------------------------+-----
> 100teens.com | 1
> 100teens.com | 1
> altavista.digital.com | 1
> altavista.digital.com | 1
> 100teens.com | 1
> altavista.digital.com | 1
> 100teens.com | 1
> 100teens.com | 1
> 100teens.com | 1
> 100teens.com | 1
> 100teens.com | 1
> 100teens.com | 1
> 100teens.com | 1
> 100teens.com | 1
>
> select short_url, sum(asize) from log group by short_url order by 2 asc;
>
> short_url | sum
> ------------------------------+--------
> 192.41.31.176 | 0
> 195.47.21.6 | 0
> 205.147.202.62 | 0
> 206.125.241.5 | 0
> 206.138.64.60 | 0
> 206.161.225.50 | 0
> 207.207.67.10 | 0
> 207.36.65.129 | 0
> 209.1.49.20 | 0
> 209.12.95.246 | 0
> 209.193.170.101 | 0
> 209.75.20.69 | 0
> adela.zcu.cz | 0
> bx1.csj.co.jp | 0
> cds.mediaplex.com | 0
> clit12.sextracker.com | 0
> cybererotica.porncity.net | 0
> downtown.pathcom.com | 0
>
>
Robert Polasek
System specialist
OSA PragoData, a.s.
Stredni 61
602 00 Brno
Czech Republik
Phone: +420-5-41321298, Fax: +420-5-41211529
E-mail: polasek@osapd.cz
