CODEWITHSUNDEEP
mysqlsql
Moolie
Moolie

Reputation: 569

Mysql how to combine GROUP BY and COUNT here

This must be quite easy, but I cannot find a good solution myself. I have two tables:

file
+----+--------+
| id | system |
+----+--------+
|  1 |     AA |     
|  2 |     AA |     
|  3 |     BB |     
|  4 |     AA |     
+----+--------+     

feature
+----+---------+------+
| id | file_id | name |
+----+---------+------+
|  1 |       1 |    A |
|  1 |       2 |    A |
|  1 |       2 |    B |
|  1 |       3 |    B |
|  1 |       3 |    C |
|  1 |       4 |    A |
|  1 |       4 |    B |
|  1 |       4 |    C |
+----+---------+------+

and I want to count how many times a feature was added to files with a specific system. For that, I have the following query:

SELECT f.name, COUNT(*) AS nr
FROM dossier d
JOIN feature f
ON f.file_id = d.id
WHERE d.system = 'AA'
AND d.id NOT IN (3157,3168,3192)
GROUP BY f.name

which gives the desired output:

+------+----+
| name | nr |
+------+----+
|    A |  3 |
|    B |  2 |
|    C |  1 |
+------+----+

Now I also want to know the total amount of files with the same specific system. A simple separate query would be:

SELECT COUNT(*) FROM file WHERE system = 'AA' AND id NOT IN (3157,3168,3192)

I've added the extra AND id NOT IN (which is irrelevant for this example) just to show that the actual query is much more complex. If I use a separate query to get the total I would have to duplicate that complexity, so I want to avoid that by returning the total from the same query.

So how can I count the number of files in the first query?

Desired output:

+------+----+-------+
| name | nr | total |
+------+----+-------+
|    A |  3 |     3 |
|    B |  2 |     3 |
|    C |  1 |     3 |
+------+----+-------+

Upvotes: 2

Views: 365

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Here is one way using Sub-query

SELECT f.NAME,
       Count(*) AS nr,
       (SELECT Count(*)
        FROM   FILE
        WHERE  system = 'AA'
               AND id NOT IN ( 3157, 3168, 3192 )) as Total
FROM   dossier d
       JOIN feature f
         ON f.file_id = d.id
WHERE  d.system = 'AA'
       AND d.id NOT IN ( 3157, 3168, 3192 )
GROUP  BY f.NAME

Or Use CROSS JOIN

SELECT *
FROM   (SELECT f.NAME,
               Count(*) AS nr,
        FROM   dossier d
               JOIN feature f
                 ON f.file_id = d.id
        WHERE  d.system = 'AA'
               AND d.id NOT IN ( 3157, 3168, 3192 )
        GROUP  BY f.NAME) A
       CROSS JOIN (SELECT Count(*) AS Total
                   FROM   FILE
                   WHERE  system = 'AA'
                          AND id NOT IN ( 3157, 3168, 3192 )) B

Upvotes: 2

Related Questions