MySQL: multiple grouping

Question

So I have an example table called items with the following columns:

item_id (int)
person_id (int)
item_name (varchar)
item_type (varchar) - examples: "news", "event", "document"
item_date (datetime)

...and a table person with the following columns: "person_id", "person_name".

I was hoping to display a list of the top 2 submitters (+ the COUNT() of items submitted) in a given time period for each item_type. Here's basically what I was hoping the MySQL output would look like:

person_name  | item_type | item_count

Steve Jobs   | document  | 11
Bill Gates   | document  | 6
John Doe     | event     | 4
John Smith   | event     | 2
Bill Jones   | news      | 24
Bill Nye     | news      | 21

How is this possible without making a separate query for each item_type? Thanks in advance!

Quassnoi · Accepted Answer · 2009-03-27 00:47:49Z

2

SELECT  item_type, person_name, item_count
FROM    (
        SELECT  item_type, person_name, item_count,
                @r := IFNULL(@r, 0) + 1 AS rc,
                CASE WHEN @_item_type IS NULL OR @_item_type <> item_type THEN @r := 0 ELSE 1 END,
                @_item_type := item_type,
        FROM    (
                SELECT  @r := 0,
                        @_item_type := NULL
                ) vars,
                (
                SELECT  item_type, person_name, COUNT(*) AS item_count
                FROM    items
                GROUP BY
                        item_type, person_name
                ORDER BY
                        item_type, person_name, item_count DESC
                ) vo
        ) voi
WHERE   rc < 3

edited Mar 27, 2009 at 0:47

answered Mar 25, 2009 at 12:49

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matt Over a year ago

How efficient is as opposed to using a separate query for each item_type?

Quassnoi Over a year ago

It's more efficient. If you have lots of (item_type, person_name) pairs, it's even noticeably efficient.

Alistair Knock Over a year ago

Depends on how many item_types you actually have but doing a single trip to the database is normally a good idea, and this query looks like it won't be very demanding on resource.

Quassnoi Over a year ago

It will be really easier if you post the error code and text.

Seb · Accepted Answer · 2009-03-25 13:17:51Z

1

Something like this shoul work:

SELECT
  p.person_name, i.item_type, COUNT(1) AS item_count
FROM
  person p
  LEFT JOIN item i
    ON p.person_id = i.person_id
GROUP BY
  p.person_id,
  i.item_type
HAVING
  COUNT(1) >= (
    SELECT
      COUNT(1)
    FROM
      item i2
    WHERE
      i2.item_type = i.item_type
    GROUP BY
      i2.person_id
    LIMIT 1,1
  )

answered Mar 25, 2009 at 13:17

Seb

25.2k6 gold badges69 silver badges85 bronze badges

1 Comment

ax. Over a year ago

doesn't work: for some event types, returns only 1 top submitter even if there are more for this type.

GoatRider · Accepted Answer · 2009-03-25 13:15:40Z

1

I think this should do it:

SELECT person_name,item_type,count(item_id) AS item_count
FROM person
LEFT JOIN items USING person_id
GROUP BY person_id

The "item_type" column is going to be dodgy though, each row represents multiple items, and you're only showing the item_type from one of them. You can list all of them with "GROUP_CONCAT", that's a lot of fun.

answered Mar 25, 2009 at 13:15

GoatRider

1,2136 silver badges12 bronze badges

2 Comments

Seb Over a year ago

You're just grouping, but he needs only the top 2 submitters from each item_type.

GoatRider Over a year ago

Ah, I didn't see the part about top 2 in each category.

Collectives™ on Stack Overflow

MySQL: multiple grouping

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related