>Dear Experts
>
>Following are tow columns in Table1, Field1 has products and Fileld2 has quantity
>A-----------60
>A-----------45
>A-----------20
>B-----------40
>B-----------35
>B-----------85
>B-----------15
>C-----------12
>C-----------18
>
>In table I want to get sub totals of every category and then Grand Total at the end of the Table as below
>
>A-----------60
>A-----------45
>A-----------20
>sub total--125
>B-----------40
>B-----------35
>B-----------85
>B-----------15
>sub total--175
>C-----------12
>C-----------18
>sub total---30
>Grand total-330
>
>Please help
This is not a correct SQL solution, but it work with the VFP's sql engine:
CREATE CURSOR testsub (item C,value Y)
INSERT INTO testsub VALUES ('A',60)
INSERT INTO testsub VALUES ('B',40)
INSERT INTO testsub VALUES ('B',35)
INSERT INTO testsub VALUES ('A',45)
INSERT INTO testsub VALUES ('B',85)
INSERT INTO testsub VALUES ('C',12)
INSERT INTO testsub VALUES ('B',15)
INSERT INTO testsub VALUES ('C',18)
INSERT INTO testsub VALUES ('A',20)
SELECT item,0 Level,value FROM testsub;
UNION;
SELECT item+" SubTotal",1 Level,SUM(value) FROM testsub GROUP BY item ;
UNION ALL;
SELECT "Total",2,SUM(value) FROM testsub
With VFP9 this can to be written correctly.