sql server - Grouping Multiple Counts on Pivot Table by Year -

i have started learning sql. i've written following:

declare @datefrom date = '01-jan-2014', @dateto date = '31-dec-2014'  select totalcalls, uniquecalls, totalemails, uniqueemails, agentscontacted, instructed   (   select *,   (select year(eventdate)) year,  (select count(*) events join dbo.contacttype                      on eventcontacttype=contacttypeid                      contacttypename = 'call' , eventdate >= @datefrom , eventdate <= @dateto) totalcalls,  (select count(distinct eventagentid) events join dbo.contacttype                      on eventcontacttype=contacttypeid                      contacttypename = 'call' , eventdate >= @datefrom , eventdate <= @dateto) uniquecalls,  (select count(*) events join dbo.contacttype                      on eventcontacttype=contacttypeid                      contacttypename = 'email' , eventdate >= @datefrom , eventdate <= @dateto) totalemails,  (select count(distinct eventagentid) events join dbo.contacttype                      on eventcontacttype=contacttypeid                      contacttypename = 'email' , eventdate >= @datefrom , eventdate <= @dateto) uniqueemails,  (select count(distinct eventagentid) events  join dbo.contacttype  on eventcontacttype=contacttypeid left join (select agentid, (select case when agentdateofrecentinstruction null or                      agentdateofrecentinstruction < dateadd(month, -12, @datefrom) --for sp change date @fromdate                     'no' else 'yes' end) instructedwithinpastyear agents) ti on events.eventagentid=ti.agentid eventtofrom='1'  , (contacttypename = 'email' or contacttypename = 'call') , instructedwithinpastyear = 'no' , (eventdate >= @datefrom , eventdate <= @dateto)) agentscontacted,  (select count(distinct eventagentid)     events      join dbo.agents     on eventagentid=agentid     join dbo.contacttype      on eventcontacttype=contacttypeid     left join (select agentid, (select case when agentdateofrecentinstruction null or                          agentdateoffirstintsruction < @datefrom --for sp change date @fromdate                         'no' else 'yes' end) instructedwithinpastyear agents) ti     on events.eventagentid=ti.agentid     eventtofrom='1'      , (contacttypename = 'email' or contacttypename = 'call')     , (agentdateofrecentinstruction <= @dateto , agentdateofrecentinstruction >= @datefrom)     , (eventdate >= @datefrom , eventdate <= @dateto)) instructed          events     join dbo.contacttype      on eventcontacttype=contacttypeid     join dbo.agents     on eventagentid=agentid )  s  pivot (count(eventagentid) year in ([2013],[2014]) ) pvt 

which returns thousands of row of this:

 totalcalls | uniquecalls | totalemails | uniqueemails | agentscontacted | instructed 169        | 106     | 202         | 125          | 24              | 15 169        | 106     | 202         | 125          | 24              | 15 169        | 106     | 202         | 125          | 24              | 15 

i want return:

 year | totalcalls | uniquecalls | totalemails | uniqueemails | agentscontacted 2014 | 169        | 106         | 202         | 125      | 24 2013 | 69         | 68          | 112         | 86       | 13 

i'm i'm doing few things wrong here wasn't able write question succinctly enough work out how searches.

(note: local variables there temporarily whilst write procedure)

you simplify query putting 1 query statement, rather series of individual queries in select statement. right now, have "select (select blah1from x), (select blah2 x)" write "select blah1, blah2 x" little creativity.

you have couple of basic flaws in code causing problem - you're aggregating every row in each of select queries, data you're returning count across records, not year want. in addition, never group results (look group by statement) you're returning thousands of rows, 1 each detail record, rather single row per year think want.

consider writing more following, uses aggregations against single query group by.

declare @datefrom date = '01-jan-2014', @dateto date = '31-dec-2014' select      year(eventdate) [year],     count(case when contacttypename = 'call' eventagentid end) totalcalls, -- might need "eventdate >= @datefrom , eventdate <= @dateto" part of case statement     count(distinct case when contacttypename = 'call' eventagentid end) uniquecalls,      count(case when contacttypename = 'email' eventagentid end) totalemails,     count(distinct case when contacttypename = 'email' eventagentid end) uniqueemails,      count(distinct          case              when                     eventtofrom='1' ,                  contacttypename in ('call', 'email') ,                  (agentdateofrecentinstruction null or  agentdateofrecentinstruction < dateadd(month, -12, @datefrom)) ,                  (eventdate >= @datefrom , eventdate <= @dateto)             eventagentid         end) agentscontacted,      count(distinct          case              when                     eventtofrom='1' ,                  contacttypename in ('call', 'email') ,                  agentdateofrecentinstruction <= @dateto , agentdateofrecentinstruction >= @datefromand                  (eventdate >= @datefrom , eventdate <= @dateto)             eventagentid         end) agentscontacted       dbo.events      inner join      dbo.contacttype on          eventcontacttype=contacttypeid      inner join      dbo.agents on          eventagentid=agentid group year(eventdate) 

lacking sample data or schema, can't guarantee conversion work without tweaks - you'll have take code, check spelling or syntax errors, go through each statement make sure it's doing want to. should, however, started.