Wrong result in query with left join

Question

For my project I have a SQLite DB like the following:

https://sqliteonline.com/#fiddle-5aba37c986797mugjf9muddm

And I'm trying to obtain information about this with the query:

select 
     a.id_interno, 
     a.id_esterno, 
     count(b.id_ingrediente), 
     count(c.id_utente) 
from 
     lista_spesa a 
          left join elemento_lista_spesa b on b.id_lista_spesa = a.id_interno 
          left join utente_lista_spesa c on c.id_lista_spesa = a.id_interno 
group by 
    b.id_lista_spesa, c.id_lista_spesa;

But I obtain for the counts

ID_INTERNO|ID_ESTERNO|count(b.id_ingrediente)|count(c.id_utente)
1|-1|2|2

but I expect the following result:

ID_INTERNO|ID_ESTERNO|count(b.id_ingrediente)|count(c.id_utente)
1|-1|1|2

Why I have this wrong result? How can I fix?

Thanks!

Possible duplicate of [Strange duplicate behavior from GROUP\_CONCAT of two LEFT JOINs of GROUP\_BYs](https://stackoverflow.com/questions/45250646/strange-duplicate-behavior-from-group-concat-of-two-left-joins-of-group-bys) — philipxy, Mar 29 '18 at 09:41
This is a faq: You need a join of two separate aggregations of left joins on keys. (Or equivalent.) You might have found an answer if you actually clearly stated your question/problem/goal. Always google many clear, concise & specific versions/phrasings of your question/problem/goal & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. Also you should have looked at the result of your first left join and seen if it was what you expected--part of making a [mcve]. — philipxy, Mar 29 '18 at 09:47

score 0 · Answer 1 · answered Mar 27 '18 at 12:39

Because you are joining the tables together, you have duplicate values. Try to add a distinct into the count functions:

select a.id_interno,
       a.id_esterno,
       count(DISTINCT b.id_ingrediente),
       count(DISTINCT c.id_utente) 
  from lista_spesa a
  left join elemento_lista_spesa b
    on b.id_lista_spesa = a.id_interno
  left join utente_lista_spesa c
    on c.id_lista_spesa = a.id_interno
 group by b.id_lista_spesa, c.id_lista_spesa;

I know this solution but i thinked that group by were sufficient...thanks. — Claudio P, Mar 27 '18 at 12:46

score 0 · Answer 2 · edited Mar 27 '18 at 14:26

Maybe you want something like this:

select a.id_interno, a.id_esterno, (select count(id_ingrediente) from elemento_lista_spesa) as cuentab, (select count(id_utente) from utente_lista_spesa) as cuentac
from lista_spesa a left join elemento_lista_spesa b on b.id_lista_spesa = a.id_interno 
left join utente_lista_spesa c on c.id_lista_spesa = a.id_interno
group by b.id_lista_spesa, c.id_lista_spesa;

the result is:

1, -1, 1, 2

score 0 · Answer 3 · answered Mar 27 '18 at 12:49

In your situation you don't need to use Group by for this query. Just use DISTINCT inside your count() function

OPTION 1

 SELECT 
    lista_spesa.id_interno, 
    lista_spesa.id_esterno,
    count(DISTINCT elemento_lista_spesa.id_ingrediente), 
    count(DISTINCT utente_lista_spesa.id_utente) 
FROM
    lista_spesa
        left join elemento_lista_spesa on elemento_lista_spesa.id_lista_spesa  = lista_spesa.id_interno
        left join utente_lista_spesa on utente_lista_spesa.id_lista_spesa = lista_spesa.id_interno

OPTION 2

SELECT 
    lista_spesa.id_interno, 
    lista_spesa.id_esterno,
    (SELECT count(elemento_lista_spesa.id_ingrediente) FROM elemento_lista_spesa WHERE elemento_lista_spesa.id_lista_spesa  = lista_spesa.id_interno) AS COUNT1, 
    (SELECT count(utente_lista_spesa.id_utente) FROM utente_lista_spesa WHERE utente_lista_spesa.id_lista_spesa = lista_spesa.id_interno) AS COUNT2
FROM
    lista_spesa

the first solution is not good in case when lista_spesa has more records, in that case the query returns only one record — Claudio P, Mar 27 '18 at 12:56

Wrong result in query with left join

3 Answers3