| Groups | Blog | Home |
sql server clients : Aggregate Function Error
achieve, I suspect it can be done with a subquery. DDL and sample data might help as well. ML ---
For a fully working solution we would apreciate DDL and sample data.
While you're busy preparing it let me get a clear understanding of what you're trying to achieve. You need a list of dates when sales were made, each with the sum of netprice. Is that right? Maybe something like this: select <date column> ,sum(fnetprice) as SumFnetprice from somast inner join sorels on somast.fsono=sorels.fsono where (somast.fstatus<>'CANCELLED') group by <date column> order by <date column> ML ---
I am having a problem with a query statement when I try to use sum( ) on a
field in the query. For example: select somast.fsono, somast.forderdate, somast.fcompany, sorels.fduedate, sum(fnetprice) from somast inner join sorels on somast.fsono=sorels.fsono where somast.fstatus<>'CANCELLED' GROUP BY somast.fsono ORDER BY somast.fsono When I run this I get the error message as follows ************ Server: Msg 8120, Level 16, State 1, Line 1 Column 'somast.forderdate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Server: Msg 8120, Level 16, State 1, Line 1 Column 'somast.fcompany' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Server: Msg 8120, Level 16, State 1, Line 1 Column 'sorels.fduedate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. ************ Can I get around this or must I include every field in the group by clause (which will not get me the info I need)? Thanks.
VFP did not work like this and I am new to SQL so thanks for your help on
this. I want info from two tables SOMAST (Sales Order master table) and SORELS (Sales Order Detail table) I am looking for a summed total for sales orders for each due date. I will paste my query again: *** select somast.fsono, somast.forderdate, somast.fcompany, sorels.fduedate, sum(fnetprice) from somast inner join sorels on somast.fsono=sorels.fsono where somast.fstatus<>'CANCELLED' GROUP BY somast.fsono ORDER BY somast.fsono *** Can you give me an example of a subquery you mentioned? Thanks again. [quoted text, click to view] "ML" <ML@discussions.microsoft.com> wrote in message news:CB7A6CF5-F91B-423F-A4B1-9A7B1BE7650F@microsoft.com... > That's how GROUP BY works. Please explain what it is that you're trying to > achieve, I suspect it can be done with a subquery. > > DDL and sample data might help as well. > > > ML > > --- > http://milambda.blogspot.com/
I will list some sample data for you. I will make it very simple. I hope
this is what you want. I am not sure what you mean by DDL. SOMAST Sales Order Company Date 1 A Company 12/1/05 2 B Company 12/1/05 3 C Company 12/2/05 4 A Company 12/2/05 SORELS Sales Order Item Price 1 $1 1 $2 1 $2 2 $3 2 $1 3 $5 4 $3 4 $2 My query would be: Select somast.SalesOrder, Somast.Company, Somast.Date, Sum(Sorels.ItemPrice) as SumPrice from SOMAST inner join SOITEM on somast.SalesOrder=sorels.SalesOrder Group By Somast.SalesOrder Order By Somast.Date, Somast.SalesOrder My desired results would be Sales Order Company Date SumPrice 1 A Company 12/1/05 $5 2 B Company 12/1/05 $4 3 C Company 12/2/05 $5 4 A Company 12/2/05 $5 Instead it gives me the Aggregate Function error I mentioned. Thanks. [quoted text, click to view] "ML" <ML@discussions.microsoft.com> wrote in message news:1819232F-1A57-4C95-90F8-F0DAFB0F4C37@microsoft.com... > For a fully working solution we would apreciate DDL and sample data. > > While you're busy preparing it let me get a clear understanding of what > you're trying to achieve. > > You need a list of dates when sales were made, each with the sum of > netprice. Is that right? > > Maybe something like this: > > select <date column> > ,sum(fnetprice) as SumFnetprice > from somast > inner join sorels > on somast.fsono=sorels.fsono > where (somast.fstatus<>'CANCELLED') > group by <date column> > order by <date column> > > > ML > > --- > http://milambda.blogspot.com/
The GROUP BY clause needs to include all non-aggregate columns identified in
the SELECT clause. Try this: SELECT s1.fsono, s1.forderdate, s1.fcompany, s2.fduedate, sum(s1.fnetprice) FROM somast s1 INNER JOIN sorels s2 ON s1.fsono = s2.fsono WHERE s1.fstatus <> 'CANCELLED' GROUP BY s1.fsono, s1.forderdate, s1.fcompany, s2.fduedate ORDER BY s1.fsono If you don't want to "group by" the extra fields, then the next question would be whether you really need to display those values. If all you're looking for is the sum of fnetprice by fsono, then the other three fields do not need to be displayed. However, I suspect that forderdate, fcompany, and fduedate are unique to each fsono, in which case adding them to the GROUP BY clause won't make any difference in the presentation of the output. [quoted text, click to view] "Preacher Man" <nospam> wrote in message news:uZX4BIYAGHA.3268@TK2MSFTNGP10.phx.gbl... >I am having a problem with a query statement when I try to use sum( ) on a >field in the query. For example: > > select somast.fsono, somast.forderdate, somast.fcompany, sorels.fduedate, > sum(fnetprice) > from somast inner join sorels on somast.fsono=sorels.fsono > where somast.fstatus<>'CANCELLED' > GROUP BY somast.fsono > ORDER BY somast.fsono > > When I run this I get the error message as follows > ************ > Server: Msg 8120, Level 16, State 1, Line 1 > Column 'somast.forderdate' is invalid in the select list because it is not > contained in either an aggregate function or the GROUP BY clause. > Server: Msg 8120, Level 16, State 1, Line 1 > Column 'somast.fcompany' is invalid in the select list because it is not > contained in either an aggregate function or the GROUP BY clause. > Server: Msg 8120, Level 16, State 1, Line 1 > Column 'sorels.fduedate' is invalid in the select list because it is not > contained in either an aggregate function or the GROUP BY clause. > ************ > > Can I get around this or must I include every field in the group by clause > (which will not get me the info I need)? > > Thanks. >
selet som.salesOrder,som.company,som.date,sum(sor.itemPrice) SumPrice
from somast som join sorels sor group by som.salesOrder,som.company,som.date [quoted text, click to view] "Preacher Man" <nospam> wrote in message news:OSg4pSZAGHA.2356@tk2msftngp13.phx.gbl... >I will list some sample data for you. I will make it very simple. I hope >this is what you want. I am not sure what you mean by DDL. > > SOMAST > Sales Order Company Date > 1 A Company 12/1/05 > 2 B Company 12/1/05 > 3 C Company 12/2/05 > 4 A Company 12/2/05 > > SORELS > Sales Order Item Price > 1 $1 > 1 $2 > 1 $2 > 2 $3 > 2 $1 > 3 $5 > 4 $3 > 4 $2 > > My query would be: > Select somast.SalesOrder, Somast.Company, Somast.Date, > Sum(Sorels.ItemPrice) as SumPrice from SOMAST inner join SOITEM on > somast.SalesOrder=sorels.SalesOrder Group By Somast.SalesOrder > Order By Somast.Date, Somast.SalesOrder > > My desired results would be > Sales Order Company Date SumPrice > 1 A Company 12/1/05 $5 > 2 B Company 12/1/05 $4 > 3 C Company 12/2/05 $5 > 4 A Company 12/2/05 $5 > > Instead it gives me the Aggregate Function error I mentioned. > > Thanks. > > > "ML" <ML@discussions.microsoft.com> wrote in message > news:1819232F-1A57-4C95-90F8-F0DAFB0F4C37@microsoft.com... >> For a fully working solution we would apreciate DDL and sample data. >> >> While you're busy preparing it let me get a clear understanding of what >> you're trying to achieve. >> >> You need a list of dates when sales were made, each with the sum of >> netprice. Is that right? >> >> Maybe something like this: >> >> select <date column> >> ,sum(fnetprice) as SumFnetprice >> from somast >> inner join sorels >> on somast.fsono=sorels.fsono >> where (somast.fstatus<>'CANCELLED') >> group by <date column> >> order by <date column> >> >> >> ML >> >> --- >> http://milambda.blogspot.com/ > >
Hi
VFP works like this since VFP8, in order to conform with the SQL standard rules. Rule: The GROUP BY column-list must contain the same columns as the Select columnlist. In VFP9 and SQL Server you do this: SELECT somast.fsono, somast.forderdate, somast.fcompany, sorels.fduedate, ; (SELECT SUM(fnetprice) AS sum_netprince FROM Sorels ; WHERE Sorels.fsono=somast.fsono ) ; FROM Somast ; WHERE UPPER(somast.fstatus)<>'CANCELLED' ; ORDER BY smoast.fsono or this: SELECT S1.* S2.sumnetprice ; FROM Somast AS S1 JOIN ; (SELECT fsno. SUM( netprice ) FROM Sorels ; GROUP BY fsnono ) AS S2 ; ON S2.fsono=S1.fsono ; WHERE S1.status<>'CANCELLED' ORDER BY S1.fsono or this: .... GROUP BY somast.fsono, somast.forderdate, somast.fcompany, sorels.fduedate In VFP <9 you can of course dissolve these type of queries into two queries that you join. In SQL Server you do as shown above. -Anders "Preacher Man" <nospam> skrev i meddelandet news:e19EsgYAGHA.1028@TK2MSFTNGP11.phx.gbl... [quoted text, click to view] > VFP did not work like this and I am new to SQL so thanks for your help on > this. > > I want info from two tables SOMAST (Sales Order master table) and SORELS > (Sales Order Detail table) > > I am looking for a summed total for sales orders for each due date. > > I will paste my query again: > *** > select somast.fsono, somast.forderdate, somast.fcompany, sorels.fduedate, > sum(fnetprice) > from somast inner join sorels on somast.fsono=sorels.fsono > where somast.fstatus<>'CANCELLED' > GROUP BY somast.fsono > ORDER BY somast.fsono > *** > > Can you give me an example of a subquery you mentioned? > > Thanks again. > > "ML" <ML@discussions.microsoft.com> wrote in message > news:CB7A6CF5-F91B-423F-A4B1-9A7B1BE7650F@microsoft.com... >> That's how GROUP BY works. Please explain what it is that you're trying >> to >> achieve, I suspect it can be done with a subquery. >> >> DDL and sample data might help as well. >> >> >> ML >> >> --- >> http://milambda.blogspot.com/ > >
Hi Preacher,
I'd like to go a little further with what the others have said. If you don't include every item from the Select list in the Group By list, excepting the aggregates, what values do you expect to get in the other columns? Random values? When you say that including everything in the Group By doesn't get what you need, what were you expecting to get? If you think about it, getting random values means that those values have no connection whatsoever to the aggregate value. If there is a particular (predictable) value that you are expecting, how do you expect the SQL engine to choose that value? Of course, if the value is predictable then grouping on that column should get that predictable value. -- Cindy Winegarden MSCD, Microsoft Visual FoxPro MVP cindy_winegarden@msn.com www.cindywinegarden.com [quoted text, click to view] "Preacher Man" <nospam> wrote in message news:uZX4BIYAGHA.3268@TK2MSFTNGP10.phx.gbl... > select somast.fsono, somast.forderdate, somast.fcompany, sorels.fduedate, > sum(fnetprice) > from somast inner join sorels on somast.fsono=sorels.fsono > where somast.fstatus<>'CANCELLED' > GROUP BY somast.fsono > ORDER BY somast.fsono > Can I get around this or must I include every field in the group by clause > (which will not get me the info I need)?
Don't see what you're looking for? Try a search.
|
March 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |