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

John Goodwin wrote:
> 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

Wrap parentheses areound your result set and use it as an in-line view.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)