Rasgots Rasgots - 3 months ago 14
MySQL Question

SQL SELECT request using count(*) with multiple GROUP BY

I am currently in an internship and I have to create a missing view, this preventing a website to work perfectly.

This view has to contain multiple informations, but also the number of rows with each couple status/process as in the following:

+----------------+---------+----------+-----------+-----------+--------+
| status | process | project | idprocess | idproject | number |
+----------------+---------+----------+-----------+-----------+--------+
| Not applicable | AP | tms-data | 17 | 2 | 432 |
| Not applicable | AP+51 | tms-data | 18 | 2 | 432 |
| Not applicable | AR | tms-data | 19 | 2 | 432 |
| Realized | AP | tms-data | 17 | 2 | 432 |
| Realized | AP+51 | tms-data | 18 | 2 | 432 |
| Realized | AR | tms-data | 19 | 2 | 432 |
| Safety | AP | tms-data | 17 | 2 | 432 |
| Safety | AP+51 | tms-data | 18 | 2 | 432 |
| Safety | AR | tms-data | 19 | 2 | 432 |
| Ticket | AP | tms-data | 17 | 2 | 432 |
| Ticket | AP+51 | tms-data | 18 | 2 | 432 |
| Ticket | AR | tms-data | 19 | 2 | 432 |
| To be designed | AP | tms-data | 17 | 2 | 432 |
| To be designed | AP+51 | tms-data | 18 | 2 | 432 |
| To be designed | AR | tms-data | 19 | 2 | 432 |
| Validated | AP | tms-data | 17 | 2 | 432 |
| Validated | AP+51 | tms-data | 18 | 2 | 432 |
| Validated | AR | tms-data | 19 | 2 | 432 |
+----------------+---------+----------+-----------+-----------+--------+
18 rows in set (0.03 sec)


I'm using MySQL. I don't understand why it counts this way, any ideas ?

Here is the script I've been using so far :

SELECT alstom_status.name AS status,
alstom_process.name AS process,
alstom_project.name AS project,
alstom_process.idprocess AS idprocess,
alstom_project.idproject AS idproject,
count(*) AS number
FROM alstom_rule INNER JOIN alstom_status ON idstatus
INNER JOIN alstom_project ON idproject
INNER JOIN alstom_process ON idprocess
GROUP BY alstom_status.name, alstom_process.name
ORDER BY process;

Answer
This works always for me !

SELECT alstom_status.name AS status,
           alstom_process.name AS process,
           alstom_project.name AS project,
           alstom_process.idprocess AS idprocess,
           alstom_project.idproject AS idproject,
           count(*) AS number
    FROM alstom_rule INNER JOIN alstom_status ON idstatus
                     INNER JOIN alstom_project ON idproject
                     INNER JOIN alstom_process ON idprocess
    GROUP BY status, process,project,idprocess, idproject;

You may use ( group by alstom_status.name,alstom_process.name,alstom_project.name, alstom_process.idprocess,alstom_project.idproject)