[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    RE: [GENERAL] questions
From:       "Jackson, DeJuan" <djackson () cpsgroup ! com>
Date:       1998-10-21 16:56:00
[Download RAW message or body]

> 2 questions.
> 
> 1)Hi, I have this 2 tables:
> esame=> select * from libro_autore\g
> id_libro|id_autore
> --------+---------
> s1      |        5
> n3      |        1
> n2      |        2
> n1      |        1
> n1      |        4
> 
> and 
> esame=> select * from autore\g
> cognome|nome      |data_nascita|luogo_nascita|id_autore
> -------+----------+------------+-------------+---------
> King   |Stephen   |  01-12-1940|NY           |        1
> Tamaro |Susanna   |  15-02-1965|Milano       |        2
> Clancy |Tom       |  05-05-1968|Los Angeles  |        3
> Straub |Peter     |  01-01-1950|Milano       |        4
> Maraini|Dacia     |  05-04-1962|Catania      |        5
> Zorza  |Alessandra|  05-05-1974|Cernusco     |        6
> 
> Now I want to found Cognome, Nome and number of books foreach
> "id_autore"
> in
> the libro_autore table.
> I have think a SQL-statement like
> 
> SELECT a.cognome, a.nome,la.id_autore, count(*) from libro_autore la,
> autore a  group by l
> a.id_autore\g
> 
I don't completely understand you question but this could be what you
are looking for.
SELECT a.cognome, a.nome, la.id_autore, count(la.id_libro)
FROM libro_autore la, autore a
WHERE la.id_autore = a.id_autore
GROUP BY a.cognome, a.nome, la.id_autore;

> (ok this is not correct).
> Any ideas ??
> 
> 2)Now I have this table:
> esame=> select * from view1\g
> id_libro|id_utente|tipo
> --------+---------+----
> n3      |        9|   1
> (1 row)
>  
> (for the moment only 1 item, but this table can contains more and more
> items).
> I want to find a way that return me for each "id_utente" the number of
> books group by 
> "tipo".
SELECT id_utente, tipo, count(id_libro)
FROM view1
ORDER BY id_utente, tipo
GROUP BY id_utente, tipo;


> "Il divertimento e' giusto se la scimmia ci prende gusto"
> --
> Italian Linux Meeting - http://www.pluto.linux.it/meeting/
> --
> Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group
> michel@enter.it - http://ziobudda.enter.it/
> Linux Problem? Ask to linux@media.dsi.unimi.it
> "/dev/ziobudda: access to /var/tmp/beer denied, use
> /var/adm/pineapple"
> 

[prev in list] [next in list] [prev in thread] [next in thread]