From: | Erik Jones <erik(at)myemma(dot)com> |
---|---|
To: | Postgres SQL language list <pgsql-sql(at)postgresql(dot)org> |
Subject: | COALESCE and GROUP BY and AGGREGATES |
Date: | 2006-11-13 16:49:54 |
Message-ID: | 4558A232.6050801@myemma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)myemma(dot)com>
software development
emma(r)
From | Date | Subject | |
---|---|---|---|
Next Message | Volkan YAZICI | 2006-11-13 17:03:58 | Re: COALESCE and GROUP BY and AGGREGATES |
Previous Message | ivan marchesini | 2006-11-13 16:32:06 | drop a check |