How to find Max() within Count() subquery. (Beginner level)-CodePudding

I have 3 tables with following columns:

Customer(id,name,age,city,country)
Articles read (customer_id,article_id,date)
Article (id,name,author)

I have (I think) found out the find the number of times per country that an article has been read:

SELECT c.country,a.id,SUM(ar.article_id) AS total_reads FROM customer c
JOIN article a ON c.id = a.id 
JOIN article_reads ar ON c.id = ar.customer_id 
GROUP BY c.country,a.id
ORDER BY total_reads desc;

Now I want to found out most read article in each country, with outputs country and article name.

Im trying to SELECT MAX() from the earlier COUNT() output, but I seem to only get errors. And I think I should also use a DISTINCT clause to only get one distinct max result from the most read article.

SELECT (DISTINCT c.country),a.id,MAX(ar.article.id) FROM SUM(ar.article_id) AS total_reads 
FROM customer c
JOIN article a ON c.id = a.id 
JOIN article_reads ar ON c.id = ar.customer_id 
GROUP BY c.country,a.id
ORDER BY total_reads desc;

I know there is probably 100 things im doing wrong in this query, but I this is as far as Ive gotten.

Not neccessarily looking for the straight answer, but any tips for how I should go about it would be very welcome

CodePudding user response：

I think you should not create a table for views.

I would do this:

Article table with columns (id, name, author, views) and add one at each view.

Then there will be no problem with this question :)

CodePudding user response：

if you find the article read by DISTINCT country then query be like:

SELECT a.id AS article_id, a.name AS article_name, COUNT(ar.article_id) AS total_reads, c.country
FROM article_reads ar
JOIN article a ON a.id=ar.article_id
JOIN customer c ON c.id=ar.customer_id
GROUP BY c.country ORDER BY total_reads DESC