[SQL] left outer join on more than 2 tables?
Hi, I have 3 tables region: id region_name city: id city_name region_id complaint: id date city_id I would like to find the counts of complaints by region and I would like all regions to be displayed, regardless of whether or not complaints exist for that region. Is left outer join what I'm looking for? I'm stuck at this point: select r.region_name, count(1) from region r left outer join city c, complaint k on (k.city_id = c.id and r.id = c.region_id) group by r.region_name Of course this doesn't work ... Can anyone provide their insight as to how I can achieve this? Thanks in advance, C -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join on more than 2 tables?
On 16/06/2009 19:12, Rob Sargent wrote the following: Richard Broersma wrote: On Tue, Jun 16, 2009 at 2:40 PM, Rob Sargent wrote: Is there a city without a reference to region? I don't know, but the OP wanted to know complaints by region. I didn't try this, but with regionless cities, you may need a full join if you want a complete accounting of all complaints, some being logged to the null region. And wouldn't you want to count(cm.id)? Count(cm.id) and Count(*) produce the same result. But I like Count(*) more since it helps to correctly express the idea that we are counting rows per group and not cm.id(s) per group. "Same result" is not true. I loaded tables. Using count(*) you get count=1 for regions without complaints. Using count(complaint.id) you get count = 0. (The deference amount to counting the left hand side (region) vs the right hand side (complaint) which I believe is what OP is after). Thanks everyone for your help. Your solutions worked. Much appreciated. - Carol -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] group by range of values
Hello, Here's my table: db=# select * from tester order by birth_year; birth_year | salary + 1946 | 78000 1949 | 61000 1951 | 58000 1953 | 56000 1958 | 52000 1962 | 5 1965 | 45000 1967 | 6 1968 | 57000 1970 | 47000 1972 | 32000 1973 | 42000 (12 rows) How can I display the average salary grouped by decade of birth year? That is, is it possible to display the average salary of those born in the 1940's, the average salary of those born in the 1950's, average salary of those born in the 1960's, and those born in the 1970's, all in one result table? Something like: decade | average(salary) ---+- 1940 | 69500 1950 | 5.33 1960 | 53000 1970 | 40333.33 Thanks in advance, C ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] counts of groupings by date year-month
Hi, I have a table called temp access_date | active | status -++ 2009-02-01 | t | 15 2009-02-01 | f | 16 2009-02-02 | f | 17 2009-02-01 | t | 17 2009-02-02 | f | 21 2009-01-01 | t | 20 2009-01-01 | t | 21 2009-01-01 | f | 21 What I want is to be able to get counts of active by year-month. So the output would be like: year_month | count +--- 200901 | 3 200902 | 5 I tried something like SELECT to_char(access_date, 'MM') as year_month, count(year_month) FROM temp GROUP BY year_month ORDER BY year_month; but I'm unable to execute this query because the column "year_month" doesn't exist in temp table. Is it possible to get counts by year_month? Thanks for your help in advance, CC -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
