Could someone please tell me how I can combine these two statements into one. Thank you.
string cmd = "SELECT publisher, avg(ps_projectmgmt) AS ps_projectmgmt FROM (SELECT publisher, ps_projectmgmt, dateSent FROM evaluations AS a1 INNER JOIN gwstaff AS a2 ON a1.GWContact=a2.fullname WHERE a2.dept='GWPS' AND a1.pub='1' AND (((ps_projectmgmt) Is Not Null) AND ((IIf(IsNull([ps_projectmgmt]),0,Val([ps_projectmgmt])))<>0)) AND dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#) AS DataSource1 GROUP BY publisher";
string cmd1 = "SELECT publisher, avg(ps_design) AS ps_design FROM (SELECT publisher, ps_design, dateSent FROM evaluations AS a1 INNER JOIN gwstaff AS a2 ON a1.GWContact=a2.fullname WHERE a2.dept='GWPS' AND a1.pub='1' AND (((ps_design) Is Not Null) AND ((IIf(IsNull([ps_design]),0,Val([ps_design])))<>0)) AND dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#) AS DataSource1 GROUP BY publisher";
string cmd = "SELECT publisher, avg(ps_projectmgmt) AS ps_projectmgmt FROM (SELECT publisher, ps_projectmgmt, dateSent FROM evaluations AS a1 INNER JOIN gwstaff AS a2 ON a1.GWContact=a2.fullname WHERE a2.dept='GWPS' AND a1.pub='1' AND (((ps_projectmgmt) Is Not Null) AND ((IIf(IsNull([ps_projectmgmt]),0,Val([ps_projectmgmt])))<>0)) AND dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#) AS DataSource1 GROUP BY publisher";
string cmd1 = "SELECT publisher, avg(ps_design) AS ps_design FROM (SELECT publisher, ps_design, dateSent FROM evaluations AS a1 INNER JOIN gwstaff AS a2 ON a1.GWContact=a2.fullname WHERE a2.dept='GWPS' AND a1.pub='1' AND (((ps_design) Is Not Null) AND ((IIf(IsNull([ps_design]),0,Val([ps_design])))<>0)) AND dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#) AS DataSource1 GROUP BY publisher";
commonCommand.CommandText = string.Format("{0};{1}", cmd , cmd1);
"…Mark As Answer" if my reply helpful to you…”
Thanks,
Manish Prajapati
You have to create one sql statement in order to put them in one table.
Thus you have to choose either to have three columns (publisher, ps_projectmgmt, ps_design) or to combine the two columns in one with the same name (publisher, AVG).
The first one is like:
select a.publisher, avg(a.ps_projectmgmt) as projectmgmt, avg(b.ps_design) as ps_design from (...) as a, (...) as b where a.publisher = b.publisher
for the second choise the query is:
select publisher, AVG from (select publisher, avg(ps_projectmngmt) as AVG ...) as a union (select publisher, avg(ps_design) as AVG) as b
lm111
Member
89 Points
126 Posts
combining statements
Sep 26, 2011 04:39 PM|LINK
Hello,
Could someone please tell me how I can combine these two statements into one. Thank you.
manishprajap...
Contributor
3168 Points
657 Posts
Re: combining statements
Sep 26, 2011 06:07 PM|LINK
string cmd = "SELECT publisher, avg(ps_projectmgmt) AS ps_projectmgmt FROM (SELECT publisher, ps_projectmgmt, dateSent FROM evaluations AS a1 INNER JOIN gwstaff AS a2 ON a1.GWContact=a2.fullname WHERE a2.dept='GWPS' AND a1.pub='1' AND (((ps_projectmgmt) Is Not Null) AND ((IIf(IsNull([ps_projectmgmt]),0,Val([ps_projectmgmt])))<>0)) AND dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#) AS DataSource1 GROUP BY publisher"; string cmd1 = "SELECT publisher, avg(ps_design) AS ps_design FROM (SELECT publisher, ps_design, dateSent FROM evaluations AS a1 INNER JOIN gwstaff AS a2 ON a1.GWContact=a2.fullname WHERE a2.dept='GWPS' AND a1.pub='1' AND (((ps_design) Is Not Null) AND ((IIf(IsNull([ps_design]),0,Val([ps_design])))<>0)) AND dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#) AS DataSource1 GROUP BY publisher"; commonCommand.CommandText = string.Format("{0};{1}", cmd , cmd1);Thanks,
Manish Prajapati
lm111
Member
89 Points
126 Posts
Re: combining statements
Sep 26, 2011 07:06 PM|LINK
It's not working. It says 'characters found after end of sql statement'. Any idea why?
manishprajap...
Contributor
3168 Points
657 Posts
Re: combining statements
Sep 27, 2011 04:44 AM|LINK
Here is the example:-
http://pietschsoft.com/post/2004/08/22/Fill-DataSet-with-multiple-Tables-and-update-them-with-DataAdapter.aspx
http://vb.net-informations.com/dataset/dataset-multiple-tables-sqlserver.htm
Thanks,
Manish Prajapati
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: combining statements
Sep 28, 2011 02:08 AM|LINK
There must be something wrong with your Sql statement, plese debug your sql statement and paste it into SQL Management Studio to check it with.
Thx again
reven_gr
Member
376 Points
320 Posts
Re: combining statements
Sep 29, 2011 12:58 PM|LINK
You have to create one sql statement in order to put them in one table.
Thus you have to choose either to have three columns (publisher, ps_projectmgmt, ps_design) or to combine the two columns in one with the same name (publisher, AVG).
The first one is like:
select a.publisher, avg(a.ps_projectmgmt) as projectmgmt, avg(b.ps_design) as ps_design from (...) as a, (...) as b where a.publisher = b.publisher
for the second choise the query is:
select publisher, AVG from (select publisher, avg(ps_projectmngmt) as AVG ...) as a union (select publisher, avg(ps_design) as AVG) as b
hope that helped...