Problem with sql query in only_full_group_by mode

Question

Problem with sql query in only_full_group_by mode

Navigation

#1 by (1 votes)

1

I have the following problem. A table tb_cs of which I give a theoretical example:

id idcontrato idproducto cant 
1      C1        IDP1     2
2      C1        IDP2     3 
3      C1        IDP1     2
4      C1        IDP3     1

The problem is counting the amount sum of an IDP but I have to sort it by ID.

this is my query:

select idproducto idp, sum(cant) cidp 
from tb_cs where idcontrato=:idc 
group by idproducto 
order by id

with that query I get an error for the activation of only_full_group_by, but if I include ID in the select ex:

select id, idproducto idp, sum(cant) cidp from tb_cs 
where idcontrato=:idc 
group by idproducto, id 
order by id

I repeat the same IDP and that can not be.

I appreciate any help. I've already googled quantity and made any number of changes.

mysql

asked by a.xcibo 23.04.2018 в 21:16

source

1 answer

Next and Previous buttons to navigate between cells with a yellow background PHP - Problems loading data in modal Bootstrap

score 1 · Accepted Answer

I did not understand this well of only_full_group_by ... I think I have understood it better thanks to this answer in SO in English .

When GROUP BY is used, what the manager does is that it takes several rows and converts them into a row. Because of this, has to know what to do with all the merged rows where there are different values for some columns .

This is the reason why we have two options for each field that we want to select: either include it in the GROUP BY clause, or use it in an aggregate function so that the system knows how to combine the field.

For example, in your case:

id   |  idcontrato   |  idproducto   |  cant 
---------------------------------------------
1           C1              IDP1         2
2           C1              IDP2         3 
3           C1              IDP1         2
4           C1              IDP3         1

If you say GROUP BY idproducto , how will you know what id show in the result in the case of rows 1 and 3 ? Then, you can include id in GROUP BY , which will result in the same four rows you have now. Or, use an aggregate function to show how to handle id . For example, MAX(id) .

Knowing that, we could introduce that element of order that you do not want in the final results in a sub-query, in which we will tell you what we want to do with the value id , in fact, we will say to keep the smallest value of the id that you find: MIN(id) and at the same time we will order by that value.

The query would be this:

VIEW DEMO

SELECT 
    t1.idp, 
    t1.cant 
FROM 
    ( 
        SELECT  MIN(id) id, 
                idproducto idp,  
                SUM(cant) cant
        FROM tb_cs
        WHERE idcontrato='C1' 
        GROUP BY idp
        ORDER BY id
     ) t1
;

And the result:

idp   | cant
-----------
IDP1  |  4
IDP2  |  3
IDP3  |  1
IDX8  |  1