RE: [GENERAL] questions - Mailing list pgsql-general

From Jackson, DeJuan
Subject RE: [GENERAL] questions
Date
Msg-id F10BB1FAF801D111829B0060971D839F490D1E@cpsmail
Whole thread Raw
List pgsql-general
> 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"
>

pgsql-general by date:

Previous
From: Memphisto
Date:
Subject: Re: [GENERAL] datetime problems
Next
From: Herouth Maoz
Date:
Subject: Re: [GENERAL] datetime problems