Sub Query

[KurtMillikin]

I'm getting the value "CASH" here "Select SUM(shares.shares)* price AS CASH,LuFund.fund AS STYLE". The result set looks like:

CASH STYLE
$23,000 Stock
$10,000 Bond
$43,000 Stock
$15,000 Bond

I now need to total how much I have in each asset category - Stock, Bond, Etc.

I tried to use compute SUM(CASH) BY STYLE (to no avail)

Help is greatly appreciated!

 

[royjimenez]

Select SUM(shares.shares)* price AS CASH,LuFund.fund AS STYLE
GROUP BY STYLE

Regards

 

[KurtMillikin]

Thanks for your reply. I should have put the whole query in. I'm using visual Studio 2003 to create this query.

SELECT SUM(s.SHARES) * pf.PRICE AS CASH, lf.ASSET_CLASS AS STYLE
FROM
lf INNER JOIN lf ON pf.TICKER = lf.FUND_CODE
INNER JOIN
s ON lf.FUND_NO = s.FUND
WHERE (s.COMP_NO = @pNo) AND (pf.CLOSE_DATE = @pDate)
GROUP BY pf.TICKER, pf.PRICE, lf.ASSET_CLASS

Effectively I need to value each fund (this value comes from the sum of 1,000 plus transaction records * price)based on a date selected by the user then I need to get a total of those values by Style/ASSET_CLASS.

the error I get is "CASH is invalid in the slect list because it is not contained in either an aggregate function or GROUP BY clause"

I need this functionality for a number of pages on my site and have exausted the books/ documentation I have so any help you might be is greatly appreciated.

Thanks,

Kurt

 

[royjimenez]

Basic SQL syxntax with GROUP BY :

select field1, field2, sum(field3) * field4
from mytable
group by field1, field2

if u want u can do joins with tables:

select t1.field1, t2.field1, sum(t1.field3) * t1.field4
from mytable t1, mytable2 t2
where t1.key = t2.key
group by t1.field1, t1.field1

how u can see, all the fields returned by SQL MUST BE in the group by less the aggregate functions. In the example, sum mustn't be in the group by.


In your case I think that u query is wrong ( table name ) but if u want to make this Group by if should add the fields to return them.

SELECT SUM(s.SHARES) * pf.PRICE AS CASH, lf.ASSET_CLASS AS STYLE
FROM
lf INNER JOIN lf (***PF***)ON pf.TICKER = lf.FUND_CODE
INNER JOIN
s ON lf.FUND_NO = s.FUND
WHERE (s.COMP_NO = @pNo) AND (pf.CLOSE_DATE = @pDate)
GROUP BY pf.TICKER, pf.PRICE, lf.ASSET_CLASS

 

[royjimenez]

oops i wrote it wrong

select t1.field1, t2.field1, sum(t1.field3) * t1.field4
from mytable t1, mytable2 t2
where t1.key = t2.key
group by t1.field1, t2.field1

:]