From: Polášek Robert <polasek@osapd.cz>
To: pgsql-sql@postgresql.org
Subject: Create view and group bz problems
Date: Wed, 5 Aug 1998 15:26:16 +0200
Message-ID: <1EE02EA351CAD1118D610020AFF70F7007DF64@kachna.osapd.cz> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: polasek@osapd.cz
Subject: Re: Create view and group bz problems
In-Reply-To: <1EE02EA351CAD1118D610020AFF70F7007DF64@kachna.osapd.cz>
References: <1EE02EA351CAD1118D610020AFF70F7007DF64@kachna.osapd.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox