Re: Help with JOINING 3 tables
От | Tod McQuillin |
---|---|
Тема | Re: Help with JOINING 3 tables |
Дата | |
Msg-id | Pine.GSO.4.31.0101102335130.578-100000@sysadmin обсуждение исходный текст |
Ответ на | Help with JOINING 3 tables (Uro Gruber <uros@sir-mag.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: