Group by failing on Null values

This is a multi-part message in MIME format.

--Boundary_(ID_Cd2UnmHaBzpNsexySMHLDQ)
Content-type: text/plain; charset=us-ascii
Content-transfer-encoding: 7BIT

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

--Boundary_(ID_Cd2UnmHaBzpNsexySMHLDQ)
Content-type: text/html; charset=us-ascii
Content-transfer-encoding: 7BIT

<html>

<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">

<meta name=Generator content="Microsoft Word 10 (filtered)">

<style>

</style>

</head>

<body lang=EN-CA link=blue vlink=purple>

<div class=Section1>

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

</div>

</body>

</html>

--Boundary_(ID_Cd2UnmHaBzpNsexySMHLDQ)--

while you weren't looking, Caleb Simonyi-Gindele <csg [at] 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Good day.

often, I am turning triggers off and on to perform a mass operation on a
table, and i am interested how should i care of another user operations.

the scene is:
table t1 with user defined triggers
and many tables reference t1, (so FK triggers defined on t1)

the operation i want to perform on t1 makes a great load to a server
and have no use in triggers at all.
the best way to perform this operation is to delete all records, modify, an=
d
insert them back without changing any adjuscent table.
(this way takes a few seconds.)
so i turn off triggers on t1 completely (updating pg_class.reltriggers)
operate
and turn on triggers on t1.

it works fine.

the question is:

what should i do to prevent other users of data modification on the t1 and =
the
adjuscent tables while triggers is off ?

thnx.

P.S.
....what about TEXT to REGCLASS casting ?

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

> often, I am turning triggers off and on to perform a mass operation
> on a table, and i am interested how should i care of another user
> operations.
>
> the scene is:
> table t1 with user defined triggers
> and many tables reference t1, (so FK triggers defined on t1)
>
> the operation i want to perform on t1 makes a great load to a
> server and have no use in triggers at all.
> the best way to perform this operation is to delete all records,
> modify, and insert them back without changing any adjuscent table.
> (this way takes a few seconds.)
> so i turn off triggers on t1 completely (updating
> pg_class.reltriggers) operate
> and turn on triggers on t1.
>
> it works fine.
>
> the question is:
>
> what should i do to prevent other users of data modification on the
> t1 and the adjuscent tables while triggers is off ?

If I understand your question correctly you should use a transaction
and lock the table;

begin transaction;
lock t1 in access exclusive mode;

Turn off triggers and do your updates.
(Note, "truncate t1" is faster than "delete from t1" followed by a
"vacuum full" and you might consider running "reindex table t1" after
your mass update or if appropriate drop your indexes, load the data,
then recreate them.)

Re-establish triggers.

commit; --end of transaction unlocks the table

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo [at] postgresql.org so that your
message can get through to the mailing list cleanly

thnx.

i try to sound the idea to ensure myself that you are right.

> begin transaction;
> lock t1 in access exclusive mode;
>
> Turn off triggers and do your updates.
> (Note, "truncate t1" is faster than "delete from t1" followed by a
> "vacuum full" and you might consider running "reindex table t1" after
> your mass update or if appropriate drop your indexes, load the data,
> then recreate them.)
>
> Re-establish triggers.
>
> commit; --end of transaction unlocks the table

in case another user inserts a record into an adjuscent table with the value
of reference field NOT IN t1.
the constraint causes reading of t1 to look up FK value IN t1.
so my EXCLUSIVE lock prevents even reading and this user operation will be
queued.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo [at] postgresql.org

Group by failing on Null values

Re: Group by failing on Null values

locks and triggers. give me an advice please

Re: locks and triggers. give me an advice please

Re: locks and triggers. give me an advice please