Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 15th, 2007, 04:16 PM
Authorized User
 
Join Date: Oct 2006
Posts: 10
Thanks: 0
Thanked 1 Time in 1 Post
Default group by: display different column

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

 
Old February 16th, 2007, 04:33 AM
Authorized User
 
Join Date: Jun 2003
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old February 16th, 2007, 07:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old February 16th, 2007, 12:54 PM
Authorized User
 
Join Date: Oct 2006
Posts: 10
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.






Similar Threads
Thread Thread Starter Forum Replies Last Post
XSLT simulation of two column Group By wholden XSLT 2 March 31st, 2008 08:57 AM
Sum a column at the group level? srussell705 SharePoint Development 0 October 3rd, 2007 08:40 AM
DataGrid Column Display degie C# 2005 0 May 28th, 2007 01:48 AM
Restart new group number in Group Footer sukarso Crystal Reports 2 October 13th, 2006 12:11 PM
display DataGrid column headers vertically neethling ASP.NET 1.0 and 1.1 Professional 0 May 13th, 2004 10:03 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.