Aggregate Function Error

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.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/|||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/|||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/|||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/|||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.
"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.
>|||look up technique 1 here:
http://www.devx.com/dbzone/Article/30149|||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
"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...
>|||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...
> 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...
>|||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
"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)?