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
join invoices total
join invoices t1
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'
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'
client name Total T1
A 1382.17 123.56
B 2,557.32 2,557.32
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;