X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 45565D1B1CE for ; Mon, 19 Jul 2004 19:11:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24712-05 for ; Mon, 19 Jul 2004 22:10:57 +0000 (GMT) Received: from misav07.sasknet.sk.ca (misav07.sasknet.sk.ca [142.165.20.171]) by svr1.postgresql.org (Postfix) with ESMTP id 70C1AD1B298 for ; Mon, 19 Jul 2004 19:10:50 -0300 (ADT) Received: from oregano.sasktel.net ([142.165.20.197]) by misav07 with InterScan Messaging Security Suite; Mon, 19 Jul 2004 16:10:50 -0600 Received: from borderveygqj37 (hssx-estv-30-169.sasknet.sk.ca [142.165.30.169]) by oregano.sasktel.net (SaskTel eMessaging Service) with ESMTPA id <0I1400LK4DLYW6@oregano.sasktel.net> for pgsql-sql@postgresql.org; Mon, 19 Jul 2004 16:10:50 -0600 (CST) Date: Mon, 19 Jul 2004 16:11:05 -0600 From: Caleb Simonyi-Gindele Subject: Group by failing on Null values To: pgsql-sql@postgresql.org Message-id: <000001c46ddd$491501d0$c201a8c0@borderveygqj37> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Mailer: Microsoft Outlook, Build 10.0.4510 Content-type: multipart/alternative; boundary="Boundary_(ID_Cd2UnmHaBzpNsexySMHLDQ)" Importance: Normal X-Priority: 3 (Normal) X-MSMail-priority: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200407/120 X-Sequence-Number: 18372 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

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)--