mysql - SQL Server Duplication and correct assignment of Joins -

mysql - SQL Server Duplication and correct assignment of Joins -

i want resulting table illustration table have provided, though month provided 5, want table display 12 months if amount 0 other months.

this query i'm using,

select distinct t1.employeeid, t1.name, t.monthid, t1.totalamount, t1.totalquantity (select s.employeeid, e.name, year(max([date])) [year], month(max([date])) [montht], isnull(sum(amount), 0) totalamount, isnull(sum(quantity), 0) totalquantity sales s inner bring together employee e on e.employeeid = s.employeeid grouping s.employeeid, e.name, year([date]), month([date])) t1 inner bring together (select 'january' month , 1 monthid union select 'february' month , 2 monthid union select 'march' month , 3 monthid union select 'april' month , 4 monthid union select 'may' month , 5 monthid union select 'june' month , 6 monthid union select 'july' month , 7 monthid union select 'august' month , 8 monthid union select 'september' month , 9 monthid union select 'october' month , 10 monthid union select 'november' month , 11 monthid union select 'december' month , 12 monthid) t on t1.montht <> t.monthid t.monthid not in (select month(max([date])) [montht] sales s inner bring together employee e on e.employeeid = s.employeeid , e.employeeid = 1 grouping s.employeeid, e.name, year([date]), month([date])) order t1.name

employee table (example, not total table),

+--+--+------+ |id| name | +--+---------+ |1 |john doe | +--+---------+ |2 |jane doe | +--+---------+

sales table (example, not total table),

+--+------+---------+-------+--------+ |id|saleid| date |amount |quantity| +--+------+---------+-------+--------+ |1 | 1 |5-14-2014|300 |12 | +--+------+---------+-------+--------+ |1 | 2 |5-16-2014|600 |4 | +--+------+---------+-------+--------+ |2 | 3 |5-14-2014|452 |10 | +--+------+---------+-------+--------+ |2 | 4 |5-16-2014|356 |2 | +--+------+---------+-------+--------+

i'm getting result, http://i61.tinypic.com/xnumpz.png

the problem result duplication, , 3 values (the ones duplicating) suppose displayed on 4, 5, 6

like so,

+-------+-----------+ |monthid|totalamount| +-------+-----------+ |4 |757.00 | +-------+-----------+ |5 |834.00 | +-------+-----------+ |6 |880.00 | +-------+-----------+

while since other months have value of 0.

i ended using pivot solved problem in magnificent way, same problem looking around.

declare @year int set @year = 2014 select [employee id], name, @year [year], isnull(january, 0) january, isnull(february, 0) february, isnull(march, 0) march, isnull(april, 0) april, isnull(may, 0) may, isnull(june, 0) june, isnull(july, 0) july, isnull(august, 0) august, isnull(september, 0) september, isnull(october, 0) october, isnull(november, 0) november, isnull(december, 0) dec (select s.employeeid [employee id], e.name, year([date]) [year], datename(month, [date]) [month], isnull(sum(amount), 0) totalamount sales s left outer bring together employee e on s.employeeid = e.employeeid grouping s.employeeid, e.name, year([date]), datename(month, [date])) monthlysale pivot(sum(totalamount) month in ([january], [february], [march], [april], [may], [june], [july], [august], [september], [october], [november], [december])) mypivot

mysql sql