Volkan YAZICI wrote:
On Nov 13 10:49, Erik Jones wrote:
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.
What about such a schema design:
CREATE TABLE sale_types (
id serial PRIMARY KEY,
name text NOT NULL DEFAULT 'setup'
);
CREATE UNIQUE INDEX sales_types_typ_idx ON sales_types (typ);
CREATE TABLE sales_table (
typ bigint REFERENCES sale_types (id),
amount numeric NOT NULL,
sdate timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);
SELECT TYP.name, COALESCE(SUM(TBL.amount), 0)
FROM sale_types AS TYP
LEFT OUTER JOIN sales_table AS TBL ON (TYP.id = TBL.typ)
WHERE TBL.sale_date LIKE '2006-11%'
GROUP BY TYP.name;
I didn't try the above SQL queries, but I hope you understand what I
meant.
Awesome. I didn't (and couldn't) change the schema, but doing a
self-outer join on the table did the trick. Thanks!
--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
