On 08/31/11 3:39 PM, Marcos Hercules Santos wrote:
I'm newbie in Psql and I'm trying to build one function in order to
count the products for each supplier. So i'm gonna put it quite simply
though this example
Please, consider a table called books with the following fields
bookid, title, price, idPublisher
and one another table called publisher
Idpublisher, name, city, Books
Being in that last field from Publisher, called book, I gotta have the
amount of published books for each publisher.
get rid of the books field on your publisher table, thats dynamic and
changes as you add/remove books from the book table. to get that data,
try...
SELECT p.idPublisher, p.name, p.city, COUNT(b.bookid) AS books FROM
publisher p JOIN books b USING idPublisher GROUP BY p.idPublisher;
you could make this a view if its too cumbersome.
CREATE VIEW publisher_books SELECT p.idPublisher, p.name,
p.city, COUNT(b.bookid) AS books FROM publisher p JOIN books b USING
idPublisher GROUP BY p.idPublisher;
SELECT * from publisher_books;
and of course, add other WHERE conditions...
SELECT books FROM publisher_books WHERE name=?;
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
