Proper use of ROLLUP when multiple columns should be treated as one

From: John Goodwin <john_google_at_jjgoodwin.com>
Date: 25 Apr 2005 14:33:25 -0700
Message-ID: <1114464804.987899.186310@l41g2000cwc.googlegroups.com>

Hi all,

I was wondering if there is a correct way to do a roll-up on multiple columns which should be treated as one.

For example, let's say we have a table:

users

userid
first_name
last_name

Then another table

categoryid
category_name

Then another table

post_id
user_id
category_id

Then, we do a select COUNT of user posts by category. Is there a preferred way to treat the userid, first_name, and last_name as all the same item, for ROLLUP purposes?

Otherwise, you end up with rows like:
user id | first_name | last_name | category_name | posts

   1          abe        lincoln     politics        13
   1          abe        lincoln     military        35
   1          abe        lincoln                     48
   1          abe                                    48
   1                                                 48

I would like to remove the last 3 rows.

I came up with a work-around, but it requires a sub-query to force the query to realize, and then use another where to require a last_name AND first_name to both be blank, or present.

While this works, I was looking for a more elegant way.

Thanks all,

John Goodwin Received on Mon Apr 25 2005 - 16:33:25 CDT