1
string str = string.Format(@"select m.pcode, m.fyyear, m.salary, m.ta,
                           m.contigency, m.nrc, m.institcharges, m.others,
                           y.pcode as ypcode,y.fyyear as yfyear,y.yearlyalloc,
                           y.salary as sal1,y.ta as ta1,y.contigency as cont1,
                           y.nrc as nrc1,y.institcharges as inst1,
                           y.others as other1                                      
                           FROM monthly AS m inner join yearly y
                           on m.pcode=y.pcode 
                           where m.pcode=('" + DropDownList1.SelectedItem.ToString() 
                           + "' ) AND 
                             y.fyyear like('" + DropDownList2.SelectedItem.ToString()
                           + "')group by m.pcode,m.fyyear", con); 
SqlDataAdapter da = new SqlDataAdapter(str, con);
DataTable dtNew = new DataTable();
da.Fill(dtNew);
GridView1.DataSource = dtNew;
GridView1.DataBind();

in this code the monthly table contains columns which I wanted to show in a this way:

select m.pcode, m.fyyear, sum(m.salary) as msal, sum(m.ta) as mta, 
sum(m.contigency) as m cont, sum(m.nr) as mnrc, sum(m.institcharges) as minstit, 
sum(m.others) as mothers  
FROM monthly AS m 
where m.pcode=('" + DropDownList1.SelectedItem.ToString() + "' )
and m.fyyear like('" + DropDownList2.SelectedItem.ToString() + "')
group by m.pcode,m.fyyear

and yearly table data as:

"selecet y.pcode as ypcode,y.fyyear as yfyear,y.yearlyalloc,y.salary as sal1,
 y.ta as ta1,y.contigency as cont1,y.nrc as nrc1,y.institcharges as inst1,
 y.others as other1 
 FROM yearly AS y
 where y.pcode=('" + DropDownList1.SelectedItem.ToString() + "' )
 and y.fyyear like('" + DropDownList2.SelectedItem.ToString() + "') 
 group by y.pcode,y.fyyear

I want these to combine these two queries the above compound query that I have written using join is working but its not allowing me to use sum and group by function. please help

GameDroids
  • 5,584
  • 6
  • 40
  • 59
a2ulthakur
  • 11
  • 1
  • in the yearly query, you have columns that are not aggregated, so these need to be added to the group by clause ' y.yearlyalloc,y.salary as sal1, y.ta as ta1,y.contigency as cont1,y.nrc as nrc1,y.institcharges as inst1, y.others as other1 ' is ungrouped or aggregated – Christian Phillips Apr 10 '13 at 08:42
  • but i dont need them added actually i just need them as it is ..that is my problem ?? – a2ulthakur Apr 10 '13 at 08:54
  • So, do you need to group at all then in that case? – Christian Phillips Apr 10 '13 at 08:55
  • the columns in yearly table are just reference indicators for monthly table like the salary table of yearly contains the maximum amount of money allotted to that head and the monthly table salary field is the total expenditure from that allotted money – a2ulthakur Apr 10 '13 at 08:56
  • i want to display total in this way heads || yearly allotted || total expenditure ||balance salary || 20000 || 18000(now this amount here should come from monthly table from sum(m.salary).... and so on – a2ulthakur Apr 10 '13 at 08:58
  • Can you edit the question to show some data and an example of what it needs to look like? – Christian Phillips Apr 10 '13 at 09:07
  • can i show u an image of my gridview layout ?? [IMG]http://i45.tinypic.com/xty6f.jpg[/IMG] – a2ulthakur Apr 10 '13 at 09:12
  • can you see the image ?? – a2ulthakur Apr 10 '13 at 09:34
  • Looks like you could profit from learning about the [PARTITION BY](http://stackoverflow.com/questions/2404565/sql-server-difference-between-partition-by-and-group-by) function. – Dave May 15 '13 at 02:09
  • @PriyanshuAgrawal: Don't make an inconsequential edit like that. – Engineer2021 Jul 21 '14 at 19:35

0 Answers0