Group by failing on Null values - Mailing list pgsql-sql
| From | Caleb Simonyi-Gindele |
|---|---|
| Subject | Group by failing on Null values |
| Date | |
| Msg-id | 000001c46ddd$491501d0$c201a8c0@borderveygqj37 Whole thread Raw |
| Responses |
Re: Group by failing on Null values
(Rosser Schwarz <rosser.schwarz@gmail.com>)
|
| List | pgsql-sql |
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt">I have a SELECT querythat basically adds up my sales, removes credit adjustments (eg. Returns) and gives the net figure.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt">This is the query:</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt">SELECT (SELECT SUM(cli_tran_amount) FROM vetpmardetWHERE 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' </span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt">Thesubquery in there is necessary to link the credit back to the original transaction. This queryworks well as far as I’m concerned.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt"> </span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt">WhereI run into problems is when I try to break the report down by staff codes. Unfortunately, myaccounting 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 staffcode. So I modify the query like this: (add one select column and a group by clause) </span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt">SELECT dat_staff_code, (SELECT SUM(cli_tran_amount) FROM vetpmardet WHEREcli_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</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt">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 ofthe single row returned in the first query above. How do I get it to produce a row even when dat_staff_code is null?</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt"> </span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt">TIA</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt">Caleb</span></font></div>