Ask a question

Popular technologies


Latest questions:

256 sql - Reverse the array in php
920 php - Convert long numbers to short and compact
916 php - Codeigniter 4 validation error callback_check_password is not working
202 laravel - How to calculate hour two date difference in php?
733 php - Symfony project shows directory listing index instead of index page
786 php - Test Imagick Image
285 wordpress - I am getting issues with my single Post navigation in single.php with previous_post_link() and next_post_link() is not working with post type "post"
36 PHP bcrypt to PYTHON bycrypt not giving same values
832 php - How to fix error Query array failed: 1064
561 php - What to do with mysqli problems? Errors like mysqli_fetch_array(): Argument #1 must be of type mysqli_result and such
  1. Home
  2. php - Wrong use of GROUP BY
710 votes
3 answers

php - Wrong use of GROUP BY

mangohost Get the solution ↓↓↓

i'm having a real problem with a query. The database entity are:

Paziente (Codice_Fiscale, Nome_Paziente, Cognome_Paziente, Data_Nascita, Luogo_Nascita)
Appuntamento (Codice_Appuntamento, Data_Appuntamento, Ora_Appuntamento, Codice_Fiscale)
Intervento (Codice_Intervento, Tipo_Intervento, Sala_Intervento, Data_Intervento, Ora_Intervento, Codice_Fiscale_Paziente, ID_Dentista)
Dentista (ID_Dentista, Nome_Dentista, Cognome_Dentista)

I need to do a query that shows a list of appointment grouped by type of intervention. It should be something like:

Tipo_Intervento       COUNT(*)  
Chirurgia dentale      1
Cura carie             2
Estrazione dentale     1
Pulizia dentale        3

I can do this correctly when i use this query:

SELECT intervento.Tipo_Intervento, COUNT(*)
FROM intervento
GROUP BY intervento.Tipo_Intervento

But i need to link the database "intervento" to "appuntamento" ('cause it's an exercise) and when i use this:

SELECT COUNT(DISTINCT appuntamento.Codice_Appuntamento) AS Numero_Appuntamenti, intervento.Tipo_Intervento AS Tipologia_intervento
FROM appuntamento, intervento
WHERE appuntamento.Codice_Fiscale = intervento.Codice_Fiscale_Paziente
GROUP BY intervento.Tipo_Intervento

I obtain the wrong value:

Numero_Appuntamenti      Tipologia_intervento   
2                        Chirurgia dentale
4                        Cura carie
2                        Estrazione dentale
3                        Pulizia dentale

The problem is in the GROUP BY intervento.Tipo_Intervento, that increase the number of appointments, in fact if i use GROUP BY appuntamento.Codice_Appuntamento i obtain:

Numero_Appuntamenti  Tipologia_intervento   
1                    Pulizia dentale
1                    Estrazione dentale
1                    Pulizia dentale
1                    Cura carie
1                    Cura carie
1                    Pulizia dentale
1                    Estrazione dentale

That is right, but i need it to be grouped.

Does someone can help me? (forgive my bad english, as you can see it's not my language)

EDIT: This is the table "appuntamento":

Codice_Appuntamento Data_Appuntamento   Ora_Appuntamento    Codice_Fiscale  
        001           2020-05-05    16:00:00.000000     RSSMRA80A01H501U
        002           2020-07-03    16:30:00.000000     VRDGPP85E02F205P
        003          2020-06-05     16:00:00.000000     RSSMRA80A01H501U
        004          2020-08-14     17:15:00.000000     BNCGNN83D03L219B
        005           2020-08-27    17:45:00.000000     BNCGNN83D03L219B
        006          2020-07-05     16:00:00.000000     RSSMRA80A01H501U
        007          2020-09-13     17:20:00.000000     VRDGPP85E02F205P

And this the table "intervento"

Codice_Intervento Tipo_Intervento   Sala_Intervento    Data_Intervento   Ora_Intervento   Codice_Fiscale_Paziente   ID_Dentista     
    A_001          Pulizia dentale        1             2020-05-05       16:00:00.000000    RSSMRA80A01H501U            D_001
    A_002          Estrazione dentale     2             2020-07-03        16:30:00.000000   VRDGPP85E02F205P            D_003
    A_003          Pulizia dentale        1             2020-06-05        16:00:00.000000   RSSMRA80A01H501U            D_001
    A_004          Cura carie             3             2020-08-14        17:15:00.000000   BNCGNN83D03L219B            D_001
    A_005          Chirurgia dentale      5             2020-08-27        17:45:00.000000   BNCGNN83D03L219B            D_002
    A_006          Pulizia dentale        1             2020-07-05        16:00:00.000000   RSSMRA80A01H501U            D_001
    A_007          Cura carie             3             2020-09-13         17:20:00.000000  VRDGPP85E02F205P            D_002

"Codice fiscale" and "ID_Dentista" are the foreing key, thery are the primary key of "paziente" and "dentista"

493
votes

Answer

Solution:

COUNT(DISTINCT field) returns how many unique [appuntamento].[Codice_Appuntamento] are found for each [intervento].[Tipo_Intervento]. If I read your question right, you just want to remove theDISTINCT.

That should work if you use aLEFT JOIN. Your way of putting together tables rule out any [appuntamento] without a [intervento]

SELECT COUNT(appuntamento.Codice_Appuntamento) AS Numero_Appuntamenti, intervento.Tipo_Intervento AS Tipologia_intervento
FROM appuntamento
LEFT JOIN intervento ON appuntamento.Codice_Fiscale = intervento.Codice_Fiscale_Paziente
GROUP BY intervento.Tipo_Intervento

618
votes

Answer

Solution:

This will work on mysql 5.6, not on higher versions :

https://www.db-fiddle.com/f/sDXoDdMWsPVkTheoaWkx1i/0

SELECT  i.Tipo_Intervento,tmp.Numero_Appuntamenti,a.*
FROM intervento i
LEFT JOIN  appuntamento a ON a.Codice_Fiscale = i.Codice_Fiscale_Paziente
LEFT JOIN
(SELECT COUNT(*) AS Numero_Appuntamenti,Tipo_Intervento 
           from intervento
           GROUP BY Tipo_Intervento)  AS tmp ON i.Tipo_Intervento=tmp.Tipo_Intervento
GROUP BY i.Tipo_Intervento

105
votes

Answer

Solution:

You can use this query:

SELECT max(i.Tipo_Intervento), count(a. Codice_Appuntamento)
FROM intervento as i left join appuntamento as a on i.Codice_Fiscale_Paziente = a.Codice_Fiscale 
GROUP BY i.Sala_Intervento

Source

Share

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Similar questions

Find the answer in similar questions on our website.

453 mysql - Order by After Where clause not working in MySQLi PHP
301 php - Retrieving Role and Permission from Authenticated User Laravel Breeze + React + inertia + Spatie Permission
200 php - Put a login on top of my web space to prevent access to my subfolders
451 php - PHPUnit test not sharing the entity manager with Symfony
826 php - How to fix Connection failed: Access denied for user 'webroot'@'localhost' (using password: YES)
958 php - how to query WooCommerce products by date_modified
231 PHP | How can i select the last index of array
696 linux - PHP-FPM Exporter Binary can not access php unix socket
240 php - Is it possible to put a time restriction on user detail changes on wordpress/
48 php - How can I set a specific PhpSession Cookie for a webpage?

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.




About the technologies asked in this question

PHP

PHP (from the English Hypertext Preprocessor - hypertext preprocessor) is a scripting programming language for developing web applications. Supported by most hosting providers, it is one of the most popular tools for creating dynamic websites. The PHP scripting language has gained wide popularity due to its processing speed, simplicity, cross-platform, functionality and distribution of source codes under its own license.
https://www.php.net/


MySQL

DBMS is a database management system. It is designed to change, search, add and delete information in the database. There are many DBMSs designed for similar purposes with different features. One of the most popular is MySQL. It is a software tool designed to work with relational SQL databases. It is easy to learn even for site owners who are not professional programmers or administrators. MySQL DBMS also allows you to export and import data, which is convenient when moving large amounts of information.
https://www.mysql.com/

Welcome to webdevask.com

Welcome to webdevask.com

Welcome to the Q&A site for web developers. Here you can ask a question about the problem you are facing and get answers from other experts. We have created a user-friendly interface so that you can quickly and free of charge ask a question about a web programming problem. We also invite other experts to join our community and help other members who ask questions. In addition, you can use our search for questions with a solution.

Get answers to specific questions

Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.

Help Others Solve Their Issues

Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.