pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGroup by failing on Null values
2+ messages / 2 participants
[nested] [flat]
* Group by failing on Null values
@ 2004-07-19 22:11 Caleb Simonyi-Gindele <csg@bordervet.ca>
0 siblings, 1 reply; 2+ messages in thread
From: Caleb Simonyi-Gindele @ 2004-07-19 22:11 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Group by failing on Null values
@ 2004-07-19 22:29 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: Caleb Simonyi-Gindele <csg@bordervet.ca>
0 siblings, 0 replies; 2+ messages in thread
From: Rosser Schwarz @ 2004-07-19 22:29 UTC (permalink / raw)
To: Caleb Simonyi-Gindele <csg@bordervet.ca>; +Cc: pgsql-sql
while you weren't looking, Caleb Simonyi-Gindele <csg@bordervet.ca> wrote:
> How do I get it to produce a row even when dat_staff_code is null?
try something like
SELECT coalesce(dat_staff_code, 0)...GROUP BY dat_staff_code
That will substitute a zero for any NULL value in that column, which
GROUP BY should then pick up.
/rls
--
:wq
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-07-19 22:29 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-19 22:11 Group by failing on Null values Caleb Simonyi-Gindele <csg@bordervet.ca>
2004-07-19 22:29 ` Rosser Schwarz <rosser.schwarz@gmail.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