Count rows with DISTINCT(several columns) and MAX(another column)

Question

My table contains votes of users for different items. It has the following columns:

id, user_id, item_id, vote, utc_time

Only id is a unique field and the combination of user_id and utc_time is probably also unique. But user can cast votes for any item many times.

A vote is not a number but rather has one of several possible values (e.g., "awful", "bad", "good", "excellent").

I need to count how many different users cast their last vote for a given #item# as "excellent", as "good", etc. So assuming I have only four different possible vote values, I need to get four records with the following fields:

vote, count_of_users

I understand how to count all votes, not only last votes of users:

SELECT vote, COUNT(id) FROM votes WHERE item_id=#item# GROUP BY vote;

But I cannot figure out how to count only the votes where utc_time = MAX(utc_time) for each user... Thanks for your help.

This question is connected to the previous question of mine: Select one row with MAX(column) for known other several columns without subquery

score 2 · Accepted Answer · edited Jan 21 '13 at 00:31

2

try this solution if it fits with you,

SELECT  a.item_ID,
        SUM(CASE WHEN a.vote = 'awful' THEN 1 ELSE 0 END) awful,
        SUM(CASE WHEN a.vote = 'bad' THEN 1 ELSE 0 END) bad,
        SUM(CASE WHEN a.vote = 'good' THEN 1 ELSE 0 END) good,
        SUM(CASE WHEN a.vote = 'excellent' THEN 1 ELSE 0 END) excellent
FROM    tableName a
        INNER JOIN
        (
            SELECT  user_ID, MAX(utc_time) max_time
            FROM    tableName
            GROUP   BY user_ID
        ) b ON a.user_ID = b.user_ID AND
                a.utc_time = b.max_time
-- WHERE   a.item_ID = 'valueHere'
GROUP   BY a.item_ID

UPDATE 1

SELECT  a.item_ID,
        a.vote,
        COUNT(*) totalCount
FROM    tableName a
        INNER JOIN
        (
            SELECT  user_ID, MAX(utc_time) max_time
            FROM    tableName
            WHERE   item_id = 'valueHere'
            GROUP   BY user_ID
        ) b ON  a.user_ID = b.user_ID AND
                a.utc_time = b.max_time
GROUP   BY a.vote

edited Jan 21 '13 at 00:31

esp

7,314
6
49
79

answered Jan 20 '13 at 23:17

John Woo

258,903
69
498
492

As I understand I can put WHERE item_id = #item# instead of GROUP BY... What I don't like is that I have to list all possible values in the query... – esp Jan 20 '13 at 23:21
you can also do that if you want for a specific `item_id` only. the query above will show all `item_id`. – John Woo Jan 20 '13 at 23:22
it doesn't actually, it shows only one item_id, it looks like the item that was voted for the latest (and with WHERE it shows nothing, unless it is the id of the item last voted for)... The bonus is that it returns votes values as fields names, which is something I didn't hope for.... Is it possible to a) get a different record for each vote value, b) not assume that there are specific votes values? – esp Jan 20 '13 at 23:28
cool, it reminds me the ideas from here: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ Let me see if it works. – esp Jan 21 '13 at 00:21
it works, can be a bit simplified actually (edited - is it correct?)... Thanks! – esp Jan 21 '13 at 00:29
One more question: I want to add other fields from items and also WHERE by some item field rather than by item_id? An immediate idea is to insert a subquery inside to find item_id and wrap it all from the outside to connect other fields from items... But it's really ugly... Any ideas? – esp Jan 21 '13 at 01:00

Count rows with DISTINCT(several columns) and MAX(another column)

1 Answers1

Linked