From: | Erik Jones <erik(at)myemma(dot)com> |
---|---|
To: | Volkan YAZICI <yazicivo(at)ttnet(dot)net(dot)tr> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: COALESCE and GROUP BY and AGGREGATES |
Date: | 2006-11-13 18:16:52 |
Message-ID: | 4558B694.2020903@myemma.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)myemma(dot)com>
software development
emma(r)
From | Date | Subject | |
---|---|---|---|
Next Message | Rodrigo Sakai | 2006-11-13 18:27:17 | RES: Inserting data in composite types! |
Previous Message | ivan marchesini | 2006-11-13 17:48:33 | Re: drop a check |