MYSQL group and inner join
I have an article table that contains the number of article views for each day. A new record is created to hold the bill for each separate day for each article.
The query below contains the article ID and total views for the top 5 most viewed article IDs of all time:
SELECT article_id,
SUM(article_count) as cnt
FROM article_views
GROUP BY article_id
ORDER BY cnt DESC
LIMIT 5
I also have a separate article table that contains all the article fields. I want to modify the query above to join the article table and get two fields for each article id. I tried to do it below, but the score is returned incorrectly:
SELECT article_views.article_id, SUM( article_views.article_count ) AS cnt, articles.article_title, articles.artcile_url
FROM article_views
INNER JOIN articles ON articles.article_id = article_views.article_id
GROUP BY article_views.article_id
ORDER BY cnt DESC
LIMIT 5
I don't know exactly what I am doing wrong. Do I need to do a subquery?
source to share
Add articles.article_title, articles.artcile_url
to the offer GROUP BY
:
SELECT
article_views.article_id,
articles.article_title,
articles.artcile_url,
SUM( article_views.article_count ) AS cnt
FROM article_views
INNER JOIN articles ON articles.article_id = article_views.article_id
GROUP BY article_views.article_id,
articles.article_title,
articles.artcile_url
ORDER BY cnt DESC
LIMIT 5;
The reason you didn't get the correct result set is because when you select rows that are not included in GROUP BY
or in the aggregated function in the clause, SELECT
MySQL selects a random value.
source to share
You are using a MySQL (mis) function called "Hidden Columns" as the article title is not in group by
. However, this may or may not be the cause of your problem.
If the numbers are wrong then I think there is a duplicate in the article table article_id
. You can check this by running:
select article_id, count(*) as cnt
from articles
group by article_id
having cnt > 1
If any of them appears, then that is your problem. If everyone has different names, then grouping by name (as suggested by Mahmoud) will fix the problem.
If not, one way to fix it:
SELECT article_views.article_id, SUM( article_views.article_count ) AS cnt, articles.article_title, articles.artcile_url
FROM article_views INNER JOIN
(select a.* from articles group by article_id) articles
ON articles.article_id = article_views.article_id
GROUP BY article_views.article_id
ORDER BY cnt DESC
LIMIT 5
This selects an arbitrary title for the article.
source to share
Your request looks mostly to me ...
But the value returned for cnt
will depend on the article_id
UNIQUE column in the table articles
. We assume this is the primary key and the lack of schema definition is just an assumption.)
In addition, we can assume there is a foreign key between tables, that is, there are articles_view
no values in the table article_id
that do not match the value article_id
in the row from the table articles
.
To check the values of the orphan article_id, run a query, for example:
SELECT v.article_id
FROM articles_view v
LEFT
JOIN articles a
ON a.article_id = v.article_id
WHERE a.article_id IS NULL
To check the "duplicate" article_id values in articles, run a query like:
SELECT a.article_id
FROM articles a
GROUP BY a.article_id
HAVING COUNT(1) > 1
If any of these queries return strings, that could be an explanation for the behavior you are seeing.
source to share