Обсуждение: 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)