asked on

FoxPro SQL

I would like someone to help me by writing a query in FoxPro
Please see attached spreadsheet where all is explained
Many thanks
R

ps........ filename (sales) should also be selected as part of the query
FoxPro--SQL.xlsx

Pavel Celba

This sounds like a home work...
What date should be displayed in the result? Your Excel file shows min date, so the following quesry will do the same calculation:

SELECT MIN(dob) AS dob, COUNT(*) AS dob_count, SUM(buyprice) AS buyprice, SUM(sellprice) AS sellprice, ;
    SUM(sellprice-buyprice) AS p_l,  100.00*SUM(sellprice-buyprice)/SUM(buyprice) AS p_l_perc, ;
   SUM(held) AS held, SUM(sold) AS sold, 100.00*SUM(sold)/SUM(held) AS sold_perc ;
 FROM sales ;
 WHERE dob BETWEEN DATE(1954, 4, 1) AND DATE(1956, 4, 24) ;
   AND gender = "F" AND age BETWEEN 20 AND 44 ;
   AND religion = "C" AND location = "Germany"

Open in new window

Pavel Celba

You should clarify the date range (DOB BETWEEN 1-APR AND 24 APR) because date contains the year also. The last requirement to select the filename as a part of the query also does not sound so meaningfull but you may add a new column containing DBF() or ALIAS() function to the query.

Ian Bell

ASKER

Hi pcelba

Thanks for the formula.
BTW it was not 'homework' At 66 I must be the oldest student
on here LOL
Can you advise how to reduce to a one line rather than group by (fieldname).?
Thanks

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ian Bell

ASKER

Thanks pcelba
I don't feel so old after all.!!