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
raceproretiredAsked:
Who is Participating?
 
pcelbaCommented:
66 isn't so much. 82 old years programmer started with VFP 9 last year. You may look at his work: http://www.dealmaster.com/

But back to your question. To reduce the SQL command result to one line without GROUP BY needs aggregate functions in all expressions. So if you need some constant field on output then you may use MIN() or MAX() function as it is done for dob column.
0
 
pcelbaCommented:
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

0
 
pcelbaCommented:
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.
0
 
raceproretiredAuthor Commented:
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
0
 
raceproretiredAuthor Commented:
Thanks pcelba
I don't feel so old after all.!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.