Skip site navigation (1) Skip section navigation (2)

Group by failing on Null values

From: Caleb Simonyi-Gindele <csg(at)bordervet(dot)ca>
To: pgsql-sql(at)postgresql(dot)org
Subject: Group by failing on Null values
Date: 2004-07-19 22:11:05
Message-ID: 000001c46ddd$491501d0$c201a8c0@borderveygqj37 (view raw or flat)
Thread:
Lists: pgsql-sql
I have a SELECT query that basically adds up my sales, removes credit
adjustments (eg. Returns) and gives the net figure.

 

This is the query:

SELECT (SELECT SUM(cli_tran_amount) FROM vetpmardet WHERE
cli_credit_adj_trans_no IN (SELECT sys_tran_number from vetpmardet WHERE
cli_tran_trans_date BETWEEN '2004-07-11' AND '2004-07-17' )) +
SUM(cli_tran_amount) AS amount FROM vetpmardet WHERE cli_tran_trans_date
BETWEEN '2004-07-11' AND '2004-07-17' AND sys_transaction_type LIKE 'C' 

 

The subquery in there is necessary to link the credit back to the original
transaction. This query works well as far as I'm concerned.

 

Where I run into problems is when I try to break the report down by staff
codes. Unfortunately, my accounting s/w does not put a staff code with the
credit adjustment, but I do want to be able to see the net sales by staff
code. So I modify the query like this: (add one select column and a group by
clause) 

 

SELECT dat_staff_code, (SELECT SUM(cli_tran_amount) FROM vetpmardet WHERE
cli_credit_adj_trans_no IN (SELECT sys_tran_number from vetpmardet WHERE
cli_tran_trans_date BETWEEN '2004-07-11' AND '2004-07-17' )) +
SUM(cli_tran_amount) AS amount FROM vetpmardet WHERE cli_tran_trans_date
BETWEEN '2004-07-11' AND '2004-07-17' AND sys_transaction_type LIKE 'C'
GROUP BY dat_staff_code

 

But this query will not produce a row for dat_staff_code when it's value is
null. It only produces grouped rows where dat_staff_code is not null, and
the sum of those rows does not equal the value of the single row returned in
the first query above. How do I get it to produce a row even when
dat_staff_code is null?

 

TIA

Caleb

Responses

pgsql-sql by date

Next:From: Rosser SchwarzDate: 2004-07-19 22:29:32
Subject: Re: Group by failing on Null values
Previous:From: Tom LaneDate: 2004-07-19 20:04:28
Subject: Re: C++ interface problem with libpq.so.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group