Thread: COALESCE and GROUP BY and AGGREGATES
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)
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 timestampwithout 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. Regards.
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 <erik@myemma.com> software development emma(r)