How can I build a join without counting doubles in the second table?

Question

i have 2 tables and I'm trying to build a query but i don't have the correct results, can someone help me please ?

Table 1 :

code|name
74|Jhon
06|Sara
92|Fred
75|Fred
06|Sara
13|Lola

Table 2 :

code|amount|folder
06|500|1
74|135|0
13|150|0
13|100|1
92|200|1
75|250|0
06|300|1

I want to select, by name, the amount and total number of folders and number of folders with 0.

I did the following query :

select table1.name,
sum(table2.amount) as amount_tot,
count(table2.folder) as nb_folder,
sum(table2.folder) as nb_folder_ko
from table1 inner join table2 on table1.code=table2.code
group by name

I have the following results :

name|amount_tot|nb_folder|nb_folder_ko
Lola|250|2|1
Fred|450|2|1
Sara|1600|4|4
Jhon|135|1|0

As you can see, it's not correct regarding my source tables, i think my query do some double count but i don't know how to fix it, please help me :)

Could you edit in an "expected" output? So we know exactly what you're trying to achieve? — Martin, Jun 07 '20 at 17:51
This involves a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. Write separate aggregations over appropriate rows and/or aggregate a case statement picking rows; join on a unique column set. Sometimes DISTINCT aggregation picks the right values after a non-key join. (A join on a non-key of either of 2 input tables can give multiple rows for each key of each table.) — philipxy, Jun 07 '20 at 20:23
Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 07 '20 at 20:27

score 2 · Answer 1 · answered Jun 07 '20 at 18:12

You get a many to many join (instead of one to many). Apply DISTINCT before the join:

select table1.name,
sum(table2.amount) as amount_tot,
count(table2.folder) as nb_folder,
sum(table2.folder) as nb_folder_ko
from 
 ( 
   select distinct name, code 
   from table1
 ) as table1 
inner join table2 
on table1.code=table2.code
group by name

If the name/code combination is not unique switch to

   select max(name), code 
   from table1
   group by code

How can I build a join without counting doubles in the second table?

1 Answers1