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