Frankie Frankie - 4 years ago 88
MySQL Question

Join the same table duplicate rows

I'm trying to join the same table in a query, but I get duplicate rows.

What i want to get is a column with the total sum of invoices for the year, and a column for each Trimester for each client.

Here is a simplified version of the query:

select client.name, sum(total.totdoc) as TOTAL, sum(t1.totdoc) as T1
from client
join invoices total
on total.idclient=client.idclient
join invoices t1
on t1.idclient=total.idclient
where t1.date between '01/01/2016' and '30/03/2016'
and total.date between '01/01/2016' and '31/12/2016'
group by client.name
having sum(total.income) > '1000'


Example data (clients is just idclient and name):

Invoices table
Idclient totdoc date
1 123.56 '01/02/2016'
1 1,258.61 '05/05/2016'
2 2,557.32 '07/03/2016'
3 123.56 '30/11/2016'


The outpot should then be:

client name Total T1
A 1382.17 123.56
B 2,557.32 2,557.32


When I check manually for one of them, the result should be around 3.000 and I'm getting around 81.000

I tried with
sum(distinct total.totdoc)
and it works on some (the ones with different totals), but the ones that are repeated get lost.

Again, this is a simplified example, so if anything is unclear, please ask.

Thanks!

Answer Source

I think you just want conditional aggregation. Something like this:

select c.name, sum(i.totdoc) as total,
       sum(case when month(date) in (1, 2, 3) then i.totdoc end) as q1,
       sum(case when month(date) in (4, 5, 6) then i.totdoc end) as q2,
       sum(case when month(date) in (7, 8, 9) then i.totdoc end) as q3,
       sum(case when month(date) in (10, 11, 12) then i.totdoc end) as q4
from client c join
     invoices i
     on i.idclient = c.idclient
where i.date >= '2016-01-01' and i.date < '2017-01-01'
group by c.name
having sum(t.income) > 1000;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download