group by: display different column

lethe · February 15th, 2007, 04:16 PM

Let's say I have a table like this:

Code:

create table table_name (
transaction_id int,
user_id int,
name varchar
)

In reality this is a transactions table joined with a users table. It is important to note that my users table has some distinct users with the same name.

We do a summary report with something like

Code:

select case(grouping(user_id) when 0 then user_id when 1 then 'total' end) as user_id, count(transaction_id) as no_transactions
from table_name
group by user_id
with rollup

This produces a table like

Code:

user_id  no_transactions
1        20
2        30
3        10
total    60

I would very much like to see a table like

Code:

user_id  no_transactions
Alice    20
Bob      30
Alice    10
total    60

If I group by name instead of id, then the two Alices get combined into one. If I group by both name and id, then I get a summary row for every name, which I do not want. Is it possible?

Thanks

Gert · February 16th, 2007, 04:33 AM

Hi.

Can't you just group by the id's, and then do a join against the user table afterwards to get the names?

best regards
Gert

happygv · February 16th, 2007, 07:05 AM

Yes, what Gert said should work for you.

But, when you group it by both user ID and Name, it should still give you an entry for each ID. Thus you will be getting summary for each ID and still see two ALICEs. I wonder if that didn't work for you.

cheers.

_________________________
- Vijay G
Strive for Perfection

lethe · February 16th, 2007, 12:54 PM

Oh yes. I've tried Gert's suggestion, joining the grouped result set with the names. The problem with this solution is that the ordering gets borked. When you do a group by clause, the names are grouped before the result set is sorted, and the summary rows go with their groups. If you join that result set, it's impossible to regain this ordering. You end up sorted by name without regard to grouping, and all the summary rows get mixed in too. Joining destroys the grouping order.

The problem with grouping by both columns (id and name) is that I get a summary row for _every_ name. For most rows, this is redundant. I have a table with a thousand users and only two share the same name. I would like a result set that is only has a thousand rows. One for each user.

I guess what I really want to do is group by id, then map ids to names within the result set. I can do this in middleware, but that means I have to do 1000 + 1 sql queries, rather than one query. That's no good.