SQL problem with distinct and count

Question

SQL problem with distinct and count

Navigation

#1 by (0 votes)

0

I have two tables, the first call question_tags_g3 and the second questions_g3. In the question_tags_g3 table the tag field contains programming languages and some are repeated many times, and when executing this statement:

select a.tag, count(a.tag) as contador, b.creation_date 
from question_tags_g3 a, questions_g3 b 
where a.id=b.id and 
      b.creation_date > '2009-01-01' and 
      creation_date < '2017-01-01' 
group by b.creation_date, a.tag 
order by contador desc limit 50;

take me out:

Javascript 204019
Javascript 199471
Javascript 196340
Javascript 195340
Javascript 193540
...

What should I modify so that the Javascript values do not repeat themselves and only the 50 most repeated languages appear to me?

mysql sql hql hive

asked by David Isla 17.01.2018 в 15:38

source

1 answer

Validate input decimals with comma Symfony2 Rest api, method PUT, request to null with multipart / form-data

score 0 · Accepted Answer

First of all, it does not make much sense to put creation_date in your query, and even less to group by that column. If you want to obtain for example the minimum creation date, then use MIN .

On the other hand, you should use COUNT(DISTINCT columna) instead of just COUNT .

Finally, try using explicit joins instead of this old form of implicit joins:

SELECT  a.tag, 
        COUNT(DISTINCT a.tag) as contador, 
        MIN(b.creation_date) as min_creation_date
FROM question_tags_g3 a
INNER JOIN questions_g3 b 
    ON a.id = b.id
WHERE  b.creation_date > '2009-01-01' 
and creation_date < '2017-01-01' 
GROUP BY a.tag 
ORDER BY contador DESC LIMIT 50;