Hi David,
Thanks for your reply. Union is ok, but seems there are issues with Join.
SELECT id, name,
count(a1.distance) as a1_cnt, sum(a1.distance) as a1_total,
count(a2.hits) as a2_cnt, sum(a2.hits) as a2_total,
count(a3.weight) as a3_cnt, sum(a3.weight) as a3_total
FROM users
LEFT JOIN activity1 a1 on users.id = a1.user_id
LEFT JOIN activity2 a2 on users.id = a2.user_id
LEFT JOIN activity3 a3 on users.id = a3.user_id
GROUP BY id, name;
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?
David Bolen-2 wrote:
>
> Peter Jin <jinyup...@gmail.com> writes:
>
>> We have an audit system which uses separate tables with similar structure
>> to
>> store different kinds of activities for a user. We want to report the
>> number
>> of activities per activity type per user.
>
> Still sounds pretty standard in terms of database schema. Can I
> assume that these separate activity tables have some field or unique
> key that correlates back to a user table or table containing per-user
> information? If so, then unless the tables are stored in separate
> database servers, it sounds like you just want to join those tables as
> part of the primary query without much hassle.
>
> Dumb example - you have a set of users, who can get rows added to one of
> three activity tables, related to three activities that have different
> metrics. Each row in an activity table is a single instance of that
> user performing that activity:
>
> users
> id integer primary key
> name text
>
> activity1
> user_id integer -- foreign key references users(id)
> distance integer
>
> activity2
> user_id integer -- foreign key references users(id)
> hits integer
>
> activity3
> user_id integer -- foreign key references users(id)
> weight integer
>
> (The join will work without the database having explicit foreign key
> references but they should be there for referential integrity if the
> activity tables do have entries for users. For performance you should
> also ensure that users.id is indexed at a minimum).
>
> Then, a query like the following:
>
> SELECT id, name,
> count(a1.distance) as a1_cnt, sum(a1.distance) as a1_total,
> count(a2.hits) as a2_cnt, sum(a2.hits) as a2_total,
> count(a3.weight) as a3_cnt, sum(a3.weight) as a3_total
> FROM users
> LEFT JOIN activity1 a1 on users.id = a1.user_id
> LEFT JOIN activity2 a2 on users.id = a2.user_id
> LEFT JOIN activity3 a3 on users.id = a3.user_id
> GROUP BY id, name;
>
> would produce a result set looking like:
>
> id name a1_cnt a1_total a2_cnt a2_total a3_cnt
> a3_total
> 1 User 1 ## #### ## #### ## ####
> 2 User 2 ## #### ## #### ## ####
> ...
>
> Feed that into a JasperReports report designed for grouping by id/user
> and then you have access to all of the total information in a single
> detail line for that user, to be presented in the per-user section of
> that report.
>
> Now if you're looking for individual detail lines for each activity
> for each user, then I think your original thought of a UNION query was
> on the right track, although you're still joining within the
> individual components of the UNION to link the user to the activity.
>
> For example:
>
> SELECT * from (
>
> SELECT id, name, 'activity1' as activity,
> count(a.distance) as count, sum(a.distance) as total
> FROM users LEFT JOIN a1 a on users.id = a.user_id
> GROUP BY id, name, activity
> UNION
> SELECT id, name, 'activity2' as activity,
> count(a.hits) as count, sum(a.hits) as total
> FROM users LEFT JOIN a2 a on users.id = a.user_id
> GROUP BY id, name, activity
> UNION
> SELECT id, name, 'activity3' as activity,
> count(a.weight) as count, sum(a.weight) as total
> FROM users LEFT JOIN a3 a on users.id = a.user_id
> GROUP BY id, name, activity
>
> ) as data
>
> ORDER by id, name, activity;
>
>
> which would result in a result set like:
>
> id name activity count total
> 1 User 1 activity1 ## ###
> 1 User 1 activity2 ## ###
> 1 User 1 activity3 ## ###
> 2 User 2 activity1 ## ###
> 2 User 2 activity2 ## ###
> 2 User 2 activity3 ## ###
>
> which I think would work fine for Jasper to perform nested groupings
> on it, first by id/name and then by activity.
>
> I know you mentioned HQL not supporting a UNION, but JasperReport can
> make a direct SQL query to the underlying data, so I'm not sure that HQL
> needs to be involved. Also, you mentioned being concerned with the size
> of the data set, but as you can see here, you'll only get one summary
> row per user, per activity, so I'm not sure how the data set can be any
> smaller (even via a mechanism other than UNION) and still provide you
> with per-activity row data for display in the report.
>
> Note that if the activity tables have columns for a name for the activity
> you could select that in lieu of the static string, though if there's any
> chance for overlap you'll then want a UNION ALL instead.
>
> Nesting the query and applying an overall ordering helps ensure that
> you get the sequence in an appropriate order for reporting, since
> otherwise an engine might produce an arbitrary ordering.
>
> Or, if the per-user section of the report is just supposed to show
> a row per activity with total values (e.g., just what this query gives),
> then there's no need to have a second level of grouping in the report,
> just take the raw query records as your rows and use them to generate
> the detail lines for the per-user information.
>
> Similarly, if you needed access to the raw activity records for each
> activity, drop the count() and sum() portions of the query in favor of
> the raw fields you care about, at which point JasperReports can do the
> summing itself during the grouping process. But that would likely be
> less efficient if you really only needed the per-activity totals
>
> And of course, at some point it's probably logical to consider hiding
> some or all of this logic behind some views on the database server
> side, thus reducing the need to have the join complexity stored in the
> report - especially if you'll have multiple reports working off of the
> same sort of query.
>
> Hope this helps. Note that none of this is too JasperReports
> specific, but is more a question on querying data from the SQL data
> source. As such, you may also find assistance in more generic
> database (especially for whatever your source database system is) or
> SQL forums.
>
> -- 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--tp23847733p23885467.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
