pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCOALESCE and GROUP BY and AGGREGATES
3+ messages / 2 participants
[nested] [flat]
* COALESCE and GROUP BY and AGGREGATES
@ 2006-11-13 16:49 Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 3+ messages in thread
From: Erik Jones @ 2006-11-13 16:49 UTC (permalink / raw)
To: 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@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: COALESCE and GROUP BY and AGGREGATES
@ 2006-11-13 17:03 Volkan YAZICI <yazicivo@ttnet.net.tr>
parent: Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 3+ messages in thread
From: Volkan YAZICI @ 2006-11-13 17:03 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-sql
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.
Regards.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: COALESCE and GROUP BY and AGGREGATES
@ 2006-11-13 18:16 Erik Jones <erik@myemma.com>
parent: Volkan YAZICI <yazicivo@ttnet.net.tr>
0 siblings, 0 replies; 3+ messages in thread
From: Erik Jones @ 2006-11-13 18:16 UTC (permalink / raw)
To: Volkan YAZICI <yazicivo@ttnet.net.tr>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-11-13 18:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-13 16:49 COALESCE and GROUP BY and AGGREGATES Erik Jones <erik@myemma.com>
2006-11-13 17:03 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-11-13 18:16 ` Erik Jones <erik@myemma.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox