I have an Orders table with persons and toys. Sample values are:

person   toy
1        puzzle
1        puzzle
1        doll
1        game
2        puzzle
2        doll
3        doll
3        game
3        game
4        doll
4        game
5        doll
5        doll

Persons may have several toys with the same or different names. I'm only interested in persons with games or puzzles. The query results I want from the above table are:

toycount   personcount
    3          1
    2          1
    1          2

In English I'd say: Show me a descending count of the number of toys ordered (puzzles and games only) and the number of people who ordered that many. It seems like this should be easy, but I'm just not getting it! Thanks for any help.

    Requires Free Membership to View

The trick here is to do two levels of aggregation. The first level is to count how many of the specified toys (puzzles and games only) were ordered by each person. This gives the "toycount" number for each person. The second level of aggregation is to count how many persons ordered each toycount. Fortunately, it is easy to nest these two levels of aggregation.

select toycount
     , count(*) as personcount
  from (
       select person
            , count(*) as toycount
         from orders
        where toy in ('puzzle','game')
       group
           by person
       ) as person_toycounts
group
    by toycount
order
    by toycount desc

Simple, yes?

This was first published in January 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: