Gossamer Forum
Home : Products : DBMan SQL : Discussion :

grouping related tables

Quote Reply
grouping related tables
Hi,

In my famous Books and Authors example (each author writes multiple books, each book has one author), the Books table contains a column with the price of the book. Now I want to show a table with for each author the sum of the prices of all books for that author. So I have the following tables:
Code:
Author: id, name
Book: id, author_id, title, price
And I want the following query:
Code:
SELECT a.id, a.name, SUM(b.price)
FROM Author a, Book b
WHERE a.id = b.author_id
GROUP BY a.id, a.name
Is it possible to get these results by only changing the templates? Or do I need to write a plugin?

Jasper

http://www.bookings.org
Quote Reply
Re: [jaspercram] grouping related tables In reply to
Hi Jasper,

As you know, I'm no expert on this kind of thing, but it seems like you could accomplish this with a well-written global. The one you and Jai helped me out with in this thread: http://www.gossamer-threads.com/...orum.cgi?post=223071 is an example of one that loops through related records in a foreign table and outputs information. Couldn't you do the same kind of thing here?

Also, I haven't quite figured out how to work with DBMan SQL's new query features, but it seems like this kind of problem might be what they're designed to handle.

Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund
Quote Reply
Re: [hennagaijin] grouping related tables In reply to
Works great! Thanks!Cool

http://www.bookings.org