List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:May 21 1999 5:44am
Subject:Re: SQL Question...
View as plain text  
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: +49 941 94 65 939
SPiN GmbH             http://www.spin.de        fax: +49 941 94 65 938
======= Web design - Java chats - Guestbooks - Java/CGI coding =======

Attachment: [application/pgp-signature]
Thread
SQL Question...Chris21 May
  • Re: SQL Question...Benjamin Pflugmann21 May