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
Ian BellretiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ian BellretiredAuthor 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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ian BellretiredAuthor Commented:
Thanks pcelba
I don't feel so old after all.!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.