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
