Date: Mon, 13 Nov 2006 19:03:58 +0200
From: Volkan YAZICI <yazicivo@ttnet.net.tr>
To: Erik Jones <erik@myemma.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: COALESCE and GROUP BY and AGGREGATES
Message-ID: <20061113170358.GA1403@alamut>
References: <4558A232.6050801@myemma.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4558A232.6050801@myemma.com>
User-Agent: Mutt/1.4.2.1i

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.