Message-ID: <4558A232.6050801@myemma.com>
Date: Mon, 13 Nov 2006 10:49:54 -0600
From: Erik Jones <erik@myemma.com>
User-Agent: Thunderbird 1.5.0.7 (Windows/20060909)
MIME-Version: 1.0
To: Postgres SQL language list <pgsql-sql@postgresql.org>
Subject: COALESCE and GROUP BY and AGGREGATES
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ok, here's a sample table for the question I have:

CREATE TABLE sales_table (
sale_type varchar default 'setup' not null,
sale_amount numeric not null
sale_date timestamp without timezone default now());

So, let's say there are 3 different sale_types: 'setup', 'layaway', 
'the_hookup' and I want to get totals for each type in a given month:

SELECT sale_type, SUM(sale_amount)
FROM sales_table
WHERE sale_date LIKE '2006-11%'
GROUP BY sale_type;

If there hasn't been a sale of a given type in that month there won't be 
a row in the result set for that type.  I want a row for each type with 
a default of 0 if there haven't been any sales for that type yet that 
month.  I've tried:

SELECT sale_type, (COALESCE(SUM(sale_amount), 0)
FROM sales_table
WHERE sale_date LIKE '2006-11%'
GROUP BY sale_type;

but, no dice.  Any ideas?  I know I can break this out into separate 
queries for each type and the COALESCE will work, but in my real-world 
situation I have a lot more than three types and that'd be ugly.

Thanks,

-- 
erik jones <erik@myemma.com>
software development
emma(r)