Mayeul sgc Mayeul sgc - 2 months ago 6
MySQL Question

GROUP BY & COUNT with multiple parameters

I have a simple configuration :
2 tables linked in a many-to-many relation, so it gave me 3 tables.

Table author:

idAuthor INT
name VARCHAR


Table publication:

idPublication INT,
title VARCHAR,
date YEAR,
type VARCHAR,
conference VARCHAR,
journal VARCHAR


Table author_has_publication:

Author_idAuthor,
Publication_idPublication


I am trying to get all the authors name that have published at least 2 papers in conference SIGMOD and conference PVLDB.
Right now I achieved this but I still have a double result. My query :

SELECT author.name, publication.journal, COUNT(*)
FROM author
INNER JOIN author_has_publication
ON author.idAuthor = author_has_publication.Author_idAuthor
INNER JOIN publication
ON author_has_publication.Publication_idPublication = publication.idPublication
GROUP BY publication.journal, author.name
HAVING COUNT(*) >= 2
AND (publication.journal = 'PVLDB' OR publication.journal = 'SIGMOD');


returns

+-------+---------+----------+
| name | journal | COUNT(*) |
+-------+---------+----------+
| Renee | PVLDB | 2 |
| Renee | SIGMOD | 2 |
+-------+---------+----------+


As you can see the result is correct but doubled, as I just want 1 time the name.

Other question, how to modify the number parameter for only one conference, for example get all the author that published at least 3 SIGMOD and at least 1 PVLDB ?

Answer

If you don't care about the journal , don't select it, it is splitting your results. Also, normal filters need to be placed in the WHERE clause, not the HAVING clause :

SELECT author.name, COUNT(*)
  FROM author
 INNER JOIN author_has_publication
    ON author.idAuthor = author_has_publication.Author_idAuthor
 INNER JOIN publication
    ON author_has_publication.Publication_idPublication =
       publication.idPublication
WHERE  publication.journal IN('PVLDB','SIGMOD')       
 GROUP BY author.name
HAVING COUNT(CASE WHEN publication.journal = 'SIGMOD' THEN 1 END) >= 2
   AND COUNT(CASE WHEN publication.journal = 'PVLDB' THEN 1 END) >= 2;

For the second question, use this HAVING() clause :

HAVING COUNT(CASE WHEN publication.journal = 'SIGMOD' THEN 1 END) >= 3
   AND COUNT(CASE WHEN publication.journal = 'PVLDB' THEN 1 END) >= 1;