Re: SQL Question...

[Benjamin Pflugmann]

Hi!

SELECT c.name AS Category, COUNT(s.id) AS SoundCount
FROM category=c, sound=s
GROUP BY c

will result in a part of what you want. Atm, I don't see a way to do
all you want in a single query. It would have been helpful, to mention
if you already managed to the sums for one column or not and if so, to
cite your query.

Bye,

	Benjamin.

On Thu, May 20, 1999 at 04:54:40PM -0400, chris@stripped wrote:
> 
> Hi I have the following tables in my database:
> 
> category:
>   id smallint(5)
>   name varchar(25)
> 
> image:
>   id           smallint(5)
>   category_key smallint(5)
>   description  varchar(25)
> 
> sound:
>   id           smallint(5)
>   category_key smallint(5)
>   description  varchar(25)
> 
> The 'image' and 'sound' tables contain keys which correspond to the 'id'
> field in the 'category' table. I would like to be able to get a listing by
> category of the number of images and sounds that correspond to it.
> Something like this:
> 
> +-----------------------------------+
> |Category | SoundCount | ImageCount |
> +-----------------------------------+
> |cat1     | 4          | 3          |
> |cat2     | 0          | 1          |
> |cat3     | 1          | 0          |
> |cat4     | 0          | 0          |
> +-----------------------------------+
> 
> Is this possible with a single SQL query?
> 
> --Chris
> 
> 

-- 
Benjamin Pflugmann aka Philemon                       philemon@stripped
Programming, Guestbook support                voice: [phone]
SPiN GmbH             http://www.spin.de        fax: [phone]
======= Web design - Java chats - Guestbooks - Java/CGI coding =======

Attachment: [application/pgp-signature]