vfp sql help 1

[yostyid]

I am trying to create a view. I need to find a way to sum column "b", but only for rows that have the same value in column "a" ie:
a b sum
1 3 5
1 2 5
2 1 3
2 2 3

I cant figure out how the query will work for that. I am figuring a subquery, but any help or direction would be great.

 

[rgbean]

I don't think you'll be able to create a real VIEW with that capability. To provide what I believe you want, you'd need to update every row with a like "a", anytime a value in "b" was changed!

Rick

 

[ramani]

HI

1. Create one view with columns..
a, SUM(b) AS sumB FROM myTable GROUP BY a

2. Now create another view..
table1.a, table1.b, tableView.Sumb FROM table1, view1 ;
WHERE table1.a = view1.a



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Mike Lewis]

Yostyid,

This is off the top of my head, and not tested.

Don't bother with a view. Just do two SELECTs, with an intermdediate cursor:

SELECT field-a, sum(field-a) as MySum FROM MyTable GROUP BY field-a INTO CURSOR TEMP

SELECT MyTable.field-a, MyTable.field-b, temp.Mysum FROM MyTable JOIN temp ON MyTable.field-a = Temp.field-a INTO CURSOR result

I might not have got those commands exactly right, but it should give you a start.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[yostyid]

Thanks for your input. The two views worked great, but I was shown another way that worked as well with a subquery:
*********
SELECT a, b, (SELECT SUM(b) FROM Table1 t2 where t2.a =t1.a and t2.b=t2.b) SM
FROM Table1 t1
Order by a,b
******

Thanks again.
justin

 

[Stella740pl]

Justin,
What version of VFP do you use?
I tried your query from command window in my VFP6,
and got "Invalid use of subquery".

Everybody,
do VFP7 and 8 allow use of subqueries as selected item expressions?
Thanks.

Stella

 

[yostyid]

i just got the same error. shoot. I was testing in MS SQL2000 on a test table. Didn't work as well on the real table in vfp7. I guess you cant do a sub in the select in vfp??

 

[Stella740pl]

>I guess you cant do a sub in the select in vfp??
You can, but not like this. In WHERE clause it would be fine.

Stella.

 

[Mike Lewis]

Stella is correct. In VFP, you cannot use a sub-query as one of the items in the SELECT list. In any case, a join (as shown in my earlier message) would probably be faster.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk