Create view and group bz problems - Mailing list pgsql-sql
From | Polášek Robert |
---|---|
Subject | Create view and group bz problems |
Date | |
Msg-id | 1EE02EA351CAD1118D610020AFF70F7007DF64@kachna.osapd.cz Whole thread Raw |
List | pgsql-sql |
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