pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





Message-ID: <1EE02EA351CAD1118D610020AFF70F7007DF64@kachna.osapd.cz>
Permalink:  ../1EE02EA351CAD1118D610020AFF70F7007DF64@kachna.osapd.cz/
Also on:    postgresql.org/message-id/1EE02EA351CAD1118D610020AFF70F7007DF64@kachna.osapd.cz

 · 

reply

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