Help with select statement

There are 10 profit centers that this select statement should return. It's only returning 4 profit centers when I run it. The 4 its giving me are the ones that have gljrs_account number 5040. The other 6 profit centers do not have amounts for that account and so those profit centers are not displayed. Where gljrs_account = 5058 is giving me the same problem because only 3 profit centers use that account number. I guess what I want it to do is assume 0 or null for those account numbers at the profit centers that don't use those accounts. My expected results are to have all 10 profit centers displayed. How can this be done? It works as expected if I remove accounts 5040 and 5058 from the where clause, but I need them in there to get the proper totals. SELECT T1.gljrs_prft_ctr, SUM(T1.gljrs_amount + T2.gljrs_amount), SUM (T3.gljrs_amount + T4.gljrs_amount + T5.gljrs_amount + T6.gljrs_amount + T7.gljrs_amount + T8.gljrs_amount + T9.gljrs_amount) FROM gl_jrnl_rs T1, gl_jrnl_rs T2, gl_jrnl_rs T3, gl_jrnl_rs T4, gl_jrnl_rs T5, gl_jrnl_rs T6, gl_jrnl_rs T7, gl_jrnl_rs T8, gl_jrnl_rs T9 WHERE T1.gljrs_prft_ctr = T2.gljrs_prft_ctr AND T2.gljrs_prft_ctr = T3.gljrs_prft_ctr AND T3.gljrs_prft_ctr = T4.gljrs_prft_ctr AND T4.gljrs_prft_ctr = T5.gljrs_prft_ctr AND T5.gljrs_prft_ctr = T6.gljrs_prft_ctr AND T6.gljrs_prft_ctr = T7.gljrs_prft_ctr AND T7.gljrs_prft_ctr = T8.gljrs_prft_ctr AND T8.gljrs_prft_ctr = T9.gljrs_prft_ctr AND T1.gljrs_date = T2.gljrs_date AND T2.gljrs_date = T3.gljrs_date AND T3.gljrs_date = T4.gljrs_date AND T4.gljrs_date = T5.gljrs_date AND T5.gljrs_date = T6.gljrs_date AND T6.gljrs_date = T7.gljrs_date AND T7.gljrs_date = T8.gljrs_date AND T8.gljrs_date = T9.gljrs_date AND T1.gljrs_date BETWEEN MDY(03,01,2003) and MDY(03,31,2003) AND T1.gljrs_account = 5000 AND T2.gljrs_account = 5040 AND T3.gljrs_account = 5049 AND T4.gljrs_account = 5050 AND T5.gljrs_account = 5051 AND T6.gljrs_account = 5055 AND T7.gljrs_account = 5056 AND T8.gljrs_account = 5057 AND T9.gljrs_account = 5058 GROUP BY 1 ORDER BY 1

Chad Lemmen <chad@lemmen.com> wrote: > SELECT T1.gljrs_prft_ctr, SUM(T1.gljrs_amount + T2.gljrs_amount), > SUM (T3.gljrs_amount + T4.gljrs_amount + T5.gljrs_amount + > T6.gljrs_amount + T7.gljrs_amount + T8.gljrs_amount + > T9.gljrs_amount) > FROM gl_jrnl_rs T1, gl_jrnl_rs T2, gl_jrnl_rs T3, gl_jrnl_rs T4, > gl_jrnl_rs T5, gl_jrnl_rs T6, gl_jrnl_rs T7, gl_jrnl_rs T8, > gl_jrnl_rs T9 > WHERE T1.gljrs_prft_ctr = T2.gljrs_prft_ctr > AND T2.gljrs_prft_ctr = T3.gljrs_prft_ctr > AND T3.gljrs_prft_ctr = T4.gljrs_prft_ctr > AND T4.gljrs_prft_ctr = T5.gljrs_prft_ctr > AND T5.gljrs_prft_ctr = T6.gljrs_prft_ctr > AND T6.gljrs_prft_ctr = T7.gljrs_prft_ctr > AND T7.gljrs_prft_ctr = T8.gljrs_prft_ctr > AND T8.gljrs_prft_ctr = T9.gljrs_prft_ctr > AND T1.gljrs_date = T2.gljrs_date > AND T2.gljrs_date = T3.gljrs_date > AND T3.gljrs_date = T4.gljrs_date > AND T4.gljrs_date = T5.gljrs_date > AND T5.gljrs_date = T6.gljrs_date > AND T6.gljrs_date = T7.gljrs_date > AND T7.gljrs_date = T8.gljrs_date > AND T8.gljrs_date = T9.gljrs_date > AND T1.gljrs_date BETWEEN MDY(03,01,2003) and MDY(03,31,2003) > AND T1.gljrs_account = 5000 > AND T2.gljrs_account = 5040 > AND T3.gljrs_account = 5049 > AND T4.gljrs_account = 5050 > AND T5.gljrs_account = 5051 > AND T6.gljrs_account = 5055 > AND T7.gljrs_account = 5056 > AND T8.gljrs_account = 5057 > AND T9.gljrs_account = 5058 > GROUP BY 1 > ORDER BY 1 I changed the select statement to sum each column separately and not sum them all together as I did above. I also made the T2 and T9 OUTER join tables, these are the two account numbers that are not used at each profit center. It is working this way. I guess the reason it didn't work the way I originally had it is because if you add a null to an integer you get null, is this correct? Here is what I did: SELECT T1.gljrs_prft_ctr, SUM(T1.gljrs_amount), SUM(T2.gljrs_amount), SUM(T3.gljrs_amount), SUM(T4.gljrs_amount), SUM(T5.gljrs_amount), SUM(T6.gljrs_amount), SUM(T7.gljrs_amount), SUM(T8.gljrs_amount), SUM(T9.gljrs_amount) FROM gl_jrnl_rs T1, OUTER gl_jrnl_rs T2, gl_jrnl_rs T3, gl_jrnl_rs T4, gl_jrnl_rs T5, gl_jrnl_rs T6, gl_jrnl_rs T7, gl_jrnl_rs T8, OUTER gl_jrnl_rs T9 This gives this output: gljrs_prft_ctr 198 (sum) 103659.87 (sum) (sum) 10941.65 (sum) 16605.57 (sum) 25646.03 (sum) 3573.09 (sum) 4942.92 (sum) 1714.99 (sum) I have omitted the other 9 profit centers, but they are all listed. The two null values are for account numbers 5040 and 5058 or columns T2.gljrs_amount and T9.gljrs_amount because profit center 198 does not have those products. So am I correct that I can't sum all these numbers together because those null values cause all values at this profit center to become null?

Chad Lemmen wrote: > Chad Lemmen <chad@lemmen.com> wrote: > >>SELECT T1.gljrs_prft_ctr, SUM(T1.gljrs_amount + T2.gljrs_amount), >> SUM (T3.gljrs_amount + T4.gljrs_amount + T5.gljrs_amount + >> T6.gljrs_amount + T7.gljrs_amount + T8.gljrs_amount + >> T9.gljrs_amount) >>FROM gl_jrnl_rs T1, gl_jrnl_rs T2, gl_jrnl_rs T3, gl_jrnl_rs T4, >> gl_jrnl_rs T5, gl_jrnl_rs T6, gl_jrnl_rs T7, gl_jrnl_rs T8, >> gl_jrnl_rs T9 >>WHERE T1.gljrs_prft_ctr = T2.gljrs_prft_ctr >> AND T2.gljrs_prft_ctr = T3.gljrs_prft_ctr >> AND T3.gljrs_prft_ctr = T4.gljrs_prft_ctr >> AND T4.gljrs_prft_ctr = T5.gljrs_prft_ctr >> AND T5.gljrs_prft_ctr = T6.gljrs_prft_ctr >> AND T6.gljrs_prft_ctr = T7.gljrs_prft_ctr >> AND T7.gljrs_prft_ctr = T8.gljrs_prft_ctr >> AND T8.gljrs_prft_ctr = T9.gljrs_prft_ctr >> AND T1.gljrs_date = T2.gljrs_date >> AND T2.gljrs_date = T3.gljrs_date >> AND T3.gljrs_date = T4.gljrs_date >> AND T4.gljrs_date = T5.gljrs_date >> AND T5.gljrs_date = T6.gljrs_date >> AND T6.gljrs_date = T7.gljrs_date >> AND T7.gljrs_date = T8.gljrs_date >> AND T8.gljrs_date = T9.gljrs_date >> AND T1.gljrs_date BETWEEN MDY(03,01,2003) and MDY(03,31,2003) >> AND T1.gljrs_account = 5000 >> AND T2.gljrs_account = 5040 >> AND T3.gljrs_account = 5049 >> AND T4.gljrs_account = 5050 >> AND T5.gljrs_account = 5051 >> AND T6.gljrs_account = 5055 >> AND T7.gljrs_account = 5056 >> AND T8.gljrs_account = 5057 >> AND T9.gljrs_account = 5058 >>GROUP BY 1 >>ORDER BY 1> > > I changed the select statement to sum each column separately and not sum > them all together as I did above. I also made the T2 and T9 OUTER join > tables, these are the two account numbers that are not used at each > profit center. It is working this way. I guess the reason it didn't > work the way I originally had it is because if you add a null to an > integer you get null, is this correct? Here is what I did: > > > SELECT T1.gljrs_prft_ctr, SUM(T1.gljrs_amount), SUM(T2.gljrs_amount), > SUM(T3.gljrs_amount), SUM(T4.gljrs_amount), SUM(T5.gljrs_amount), > SUM(T6.gljrs_amount), SUM(T7.gljrs_amount), > SUM(T8.gljrs_amount), SUM(T9.gljrs_amount) > FROM gl_jrnl_rs T1, OUTER gl_jrnl_rs T2, gl_jrnl_rs T3, gl_jrnl_rs T4, > gl_jrnl_rs T5, gl_jrnl_rs T6, gl_jrnl_rs T7, gl_jrnl_rs T8, > OUTER gl_jrnl_rs T9> > This gives this output: > > gljrs_prft_ctr 198 > (sum) 103659.87 > (sum) > (sum) 10941.65 > (sum) 16605.57 > (sum) 25646.03 > (sum) 3573.09 > (sum) 4942.92 > (sum) 1714.99 > (sum) > > I have omitted the other 9 profit centers, but they are all listed. > The two null values are for account numbers 5040 and 5058 or columns > T2.gljrs_amount and T9.gljrs_amount because profit center 198 does not > have those products. So am I correct that I can't sum all these numbers > together because those null values cause all values at this profit center > to become null? You're on the right track. A + B where either A or B is null yields NULL. SUM(A) ignores all nulls, but the end result is null if there are no rows or every row is null. In the first version, you were adding nulls together before summing - hence the unwanted output. In the second version, you are seeing want you want, more or less. Check out the NVL function - it is probably present in your database. SUM(NVL(T1.gljrs_amount,0) + ...) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/