Thread: Create view and group bz problems

Create view and group bz problems

From
Polášek Robert
Date:
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