Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 15th, 2007, 03:16 PM
Authorized User
 
Join Date: Oct 2006
Location: , , .
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

Reply With Quote
  #2 (permalink)  
Old February 16th, 2007, 03:33 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Norway.
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

Reply With Quote
  #3 (permalink)  
Old February 16th, 2007, 06:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
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
Reply With Quote
  #4 (permalink)  
Old February 16th, 2007, 11:54 AM
Authorized User
 
Join Date: Oct 2006
Location: , , .
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.

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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



All times are GMT -4. The time now is 02:38 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.