CODEWITHSUNDEEP
mysqlsqljoin
adit
adit

Reputation: 33674

how to sort and group by by it's count

I have the following:

SELECT DISTINCT s.username, COUNT( v.id ) AS cnt
FROM  `instagram_item_viewer` v
INNER JOIN  `instagram_shop_picture` p ON v.item_id = p.id
INNER JOIN  `instagram_shop` s ON p.shop_id = s.id
AND s.expirydate IS NULL 
AND s.isLocked =0
AND v.created >=  '2014-08-01'
GROUP BY (
s.id
)
ORDER BY cnt DESC

Basically I have an instagram_item_viewer with the following structure:

id  viewer_id  item_id  created

It tracks when a user has viewed an item and what time. So basically I wanted to find shops that has the most items viewed. I tried the query above and it executed fine, however it doesn't seem to give the appropriate data, it should have more count than what it is. What am I doing wrong?

Upvotes: 0

Views: 43

Answers (2)

Don Djoe
Don Djoe

Reputation: 705

As mentioned by @Lennart, if you have a sample data it would be helpful. Because otherwise there will be assumptions.

Try run this to debug (this is not the answer yet)

SELECT s.username, p.id, COUNT( v.id ) AS cnt
FROM  `instagram_item_viewer` v
INNER JOIN  `instagram_shop_picture` p ON v.item_id = p.id
INNER JOIN  `instagram_shop` s ON p.shop_id = s.id
    AND s.expirydate IS NULL 
    AND s.isLocked =0
    AND v.created >=  '2014-08-01'
GROUP BY (
    s.username, p.id
)
ORDER BY cnt DESC

The problem here is the store and item viewer is too far apart (i.e. bridged via shop_picture). Thus shop_picture needs to be in the SELECT statement.

Your original query only gets the first shop_picture count for that store that is why it is less than expected

Ultimately if you still want to achieve your goal, you can expand my SQL above to

SELECT x.username, SUM(x.cnt) -- or COUNT(x.cnt) depending on what you want
FROM
    (
    SELECT s.username, p.id, COUNT( v.id ) AS cnt
    FROM  `instagram_item_viewer` v
    INNER JOIN  `instagram_shop_picture` p ON v.item_id = p.id
    INNER JOIN  `instagram_shop` s ON p.shop_id = s.id
        AND s.expirydate IS NULL 
        AND s.isLocked =0
        AND v.created >=  '2014-08-01'
    GROUP BY (
        s.username, p.id
    )
    ORDER BY cnt DESC 
) x
GROUP BY x.username

Upvotes: 0

CKP
CKP

Reputation: 11

First, with a group by statement, you don't need the DISTINCT clause. The grouping takes care of making your records distinct.

You may want to reconsider the order of your tables. Since you are interested in the shops, start there.

Select s.username, count(v.id)
From instagram_shop s
  INNER JOIN instagram_shop_picture p ON p.shop_id = s.shop_id
INNER JOIN instagram_item_viewer v ON v.item_id = p.id
  AND v.created >= '2014-08-01'
WHERE s.expirydate IS NULL
  AND s.isLocked = 0
GROUP BY s.username

Give thata shot.

Upvotes: 1

Related Questions