Re: Help with JOINING 3 tables

On Mon, 8 Jan 2001, Uro Gruber wrote:

> I want to count how many v or c are in table statistics for some
> client.
>
> something like this:
>
> client_id      views     clicks
> 1                4         1
> 2                2         1
> 3                2         2

Try something like this:

SELECT b1.client_id, (SELECT count(s.stat_type)
                      FROM statistics s, banners b2
                      WHERE b2.client_id = b1.client_id
                      AND s.banner_id = b2.banner_id
                      AND s.stat_type = 'v') AS views,
                     (SELECT count(s.stat_type)
                      FROM statistics s, banners b2
                      WHERE b2.client_id = b1.client_id
                      AND s.banner_id = b2.banner_id
                      AND s.stat_type = 'c') AS clicks
FROM banners b1
GROUP by b1.client_id
ORDER by b1.client_id;

You can simplyfy this by creating a function to count the stats like this:

CREATE FUNCTION count_stats(text, text) RETURNS integer AS '
    SELECT count(s.stat_type)
        FROM statistics s, banners b
        WHERE b.client_id = $1
        AND s.banner_id = b.banner_id
        AND s.stat_type = $2
' LANGUAGE 'SQL';

Then the query becomes:

SELECT client_id, count_stats(client_id, 'v') as views,
                  count_stats(client_id, 'c') as clicks
FROM banners
GROUP by client_id
ORDER by client_id;
--
Tod McQuillin