Hi, It works! All your posts are of great help. Thanks a ton!
David Bolen-2 wrote: > > Peter Jin <jinyup...@gmail.com> writes: > >> Let's take a look at activity1 and activity2. If activity1 has more >> records >> than activity2, some of the records of activity2 will be joined multiple >> times. This is incorrect. Does it make sense? > > Drat, yes, dumb error (and obviously too quick 'n dirty test data) on > my part. I guess I let having just aggregate functions lull me into > thinking only rows were getting joined, but obviously the aggregates > are only applied after the join. Sorry about that. > > Let's try again - here's a possible replacement query that I believe > yields the correct results: > > select * from ( > > SELECT users.id, users.name, > a_1.a1_cnt, a_1.a1_total, > a_2.a2_cnt, a_2.a2_total, > a_3.a3_cnt, a_3.a3_total > > FROM users > > LEFT JOIN ( > SELECT id, name, > count(a1.distance) as a1_cnt, sum(a1.distance) as a1_total > FROM users JOIN activity1 a1 on users.id = a1.user_id > GROUP BY id, name > ) as a_1 on users.id = a_1.id > > LEFT JOIN ( > SELECT id, name, > count(a2.hits) as a2_cnt, sum(a2.hits) as a2_total > FROM users JOIN activity2 a2 on users.id = a2.user_id > GROUP BY id, name > ) as a_2 on users.id = a_2.id > > LEFT JOIN ( > SELECT id, name, > count(a3.weight) as a3_cnt, sum(a3.weight) as a3_total > FROM users JOIN activity3 a3 on users.id = a3.user_id > GROUP BY id, name > ) as a_3 on users.id = a_3.id > > ) as result > > ORDER BY id, name; > > The nested sub-selects are using an inner join, thus the final table > will have NULLs for both *_cnt and *_total if there are no matching > records for that activity. You could change it to a left join (like > the parent select) to get 0 in the *_cnt columns, but there would > still be NULLs in the *_total columns. If your report code would rather > not deal with NULLs, I'd probably just coalesce() those columns as > needed in the top level select. > > Depending on the sheer number of activities this could get more > complicated (though again, you can hide a lot behind views and/or > server side functions), but it still has the database server doing the > bulk of the aggregation so should be more efficient than looping over > the individual per-activity queries at the reporting level. > > -- David > > > ------------------------------------------------------------------------------ > OpenSolaris 2009.06 is a cutting edge operating system for enterprises > looking to deploy the next generation of Solaris that includes the latest > innovations from Sun and the OpenSource community. Download a copy and > enjoy capabilities such as Networking, Storage and Virtualization. > Go to: http://p.sf.net/sfu/opensolaris-get > _______________________________________________ > jasperreports-questions mailing list > jasperreports-questions@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/jasperreports-questions > > -- View this message in context: http://www.nabble.com/How-about-loop-a-sub-report--tp23847733p23899241.html Sent from the jasperreports-questions mailing list archive at Nabble.com. ------------------------------------------------------------------------------ OpenSolaris 2009.06 is a cutting edge operating system for enterprises looking to deploy the next generation of Solaris that includes the latest innovations from Sun and the OpenSource community. Download a copy and enjoy capabilities such as Networking, Storage and Virtualization. Go to: http://p.sf.net/sfu/opensolaris-get _______________________________________________ jasperreports-questions mailing list jasperreports-questions@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jasperreports-questions
