joelsilva
asked on
How i implement a SQL instruction that makes this.
Hi folks,
I need a SQL instruction that shows the AVERAGE sell price of the last 4 purchase of some client.
I am using this:
========================== ========== ========== =========
SELECT FIRST 4 * FROM books WHERE customer='JOEL' ORDER BY DATE_INC DESC
========================== ========== ========== =========
But with this, i just select the last 4 purchases of my client JOEL. How i show the AVERAGE sell price of these 4 last purchases?
P.S.: DATE_INC saves the date when the customer buy something. My price field called PRICE.
I deeply need it.
Best Regards.
I need a SQL instruction that shows the AVERAGE sell price of the last 4 purchase of some client.
I am using this:
==========================
SELECT FIRST 4 * FROM books WHERE customer='JOEL' ORDER BY DATE_INC DESC
==========================
But with this, i just select the last 4 purchases of my client JOEL. How i show the AVERAGE sell price of these 4 last purchases?
P.S.: DATE_INC saves the date when the customer buy something. My price field called PRICE.
I deeply need it.
Best Regards.
SELECT ... AVG(PRICE) ...
ASKER
Thx. for your comments.
I´ve tried that, but i didnt get it. I dont know how to add AVG(PRICE) in my code above. Do you know how to do it?
I´ve tried that, but i didnt get it. I dont know how to add AVG(PRICE) in my code above. Do you know how to do it?
AVG(PRICE) should be in the place of column
if you write
SELECT PRICE FROM YOUR_TABLE
it will output only columns PRICE from YOUR_TABLE.
SELECT AVG(PRICE) FROM YOUR_TABLE
this will should you the average PRICE
if you write
SELECT PRICE FROM YOUR_TABLE
it will output only columns PRICE from YOUR_TABLE.
SELECT AVG(PRICE) FROM YOUR_TABLE
this will should you the average PRICE
ASKER
I know it friend, but how to insert this AVG(PRICE) in my SQL instruction (SELECT FIRST 4 * FROM books WHERE customer='JOEL' ORDER BY DATE_INC DESC), to implement what a want?
try with
SELECT FIRST 4 AVG(PRICE)
FROM books
WHERE customer='JOEL'
ORDER BY DATE_INC DESC
SELECT FIRST 4 AVG(PRICE)
FROM books
WHERE customer='JOEL'
ORDER BY DATE_INC DESC
ASKER
Hi and thx. very much...
That didnt work... The problem i in AVG(PRICE), because, if i replace AVG(PRICE) by *, it works but the result is not what i need.
That didnt work... The problem i in AVG(PRICE), because, if i replace AVG(PRICE) by *, it works but the result is not what i need.
If your DB implementation support nested selects, you can try this:
SELECT AVG(SUBSELECT.SUBSELECT_PR ICE)
FROM
( SELECT FIRST 4 PRICE AS SUBSELECT_PRICE
FROM BOOKS
WHERE CUSTOMER='JOEL'
ORDER BY DATE_INC DESC ) SUBSELECT
SELECT AVG(SUBSELECT.SUBSELECT_PR
FROM
( SELECT FIRST 4 PRICE AS SUBSELECT_PRICE
FROM BOOKS
WHERE CUSTOMER='JOEL'
ORDER BY DATE_INC DESC ) SUBSELECT
ASKER
No way man. I am using Firebird 1.5.
I deeply thank you by your insistance.
I deeply thank you by your insistance.
Hi,
joelsilva: I need a SQL instruction that shows the AVERAGE sell price of the last 4 purchase of some client.
Ivanov G: SELECT FIRST 4 AVG(PRICE) FROM books WHERE customer='JOEL' ORDER BY DATE_INC DESC
joelsilve: That didnt work... The problem i in AVG(PRICE), because, if i replace AVG(PRICE) by *, it works but the result is not what i need.
Kate: So why are you replacing that AVG by *, you wanted AVG price, that's done by exacly what Ivanov_G suggested..
I feel a bit of misunderstanding here.. joelsilva please specify what exactly is your need ;)
regards,
Kate
joelsilva: I need a SQL instruction that shows the AVERAGE sell price of the last 4 purchase of some client.
Ivanov G: SELECT FIRST 4 AVG(PRICE) FROM books WHERE customer='JOEL' ORDER BY DATE_INC DESC
joelsilve: That didnt work... The problem i in AVG(PRICE), because, if i replace AVG(PRICE) by *, it works but the result is not what i need.
Kate: So why are you replacing that AVG by *, you wanted AVG price, that's done by exacly what Ivanov_G suggested..
I feel a bit of misunderstanding here.. joelsilva please specify what exactly is your need ;)
regards,
Kate
ASKER
That didnt worked... I just replace avg by * to confirm that the error was that...
What i´d like to do is what IVANOV wrote:
========================== =======
SELECT AVG(SUBSELECT.SUBSELECT_PR ICE)
FROM
( SELECT FIRST 4 PRICE AS SUBSELECT_PRICE
FROM BOOKS
WHERE CUSTOMER='JOEL'
ORDER BY DATE_INC DESC ) SUBSELECT
========================== =======
But it didnt worked.
What i´d like to do is what IVANOV wrote:
==========================
SELECT AVG(SUBSELECT.SUBSELECT_PR
FROM
( SELECT FIRST 4 PRICE AS SUBSELECT_PRICE
FROM BOOKS
WHERE CUSTOMER='JOEL'
ORDER BY DATE_INC DESC ) SUBSELECT
==========================
But it didnt worked.
Well..now it's clear :) so:
1) FireBird cannot make subselects (unfortunatelly)
2) The only solution is make a VIEW like this:
MY_VIEW:
select first 4
PRICE
from BOOKS
where
CUSTOMER='JOEL'
order by
DATE_INC desc
3) Then use this VIEW in select like this:
select AVG(PRICE)
from MY_VIEW
That should do the work..you can combine usage of views to achieve different results
(for example returning average PRICE from VIEW directly and so)
regards,
Kate
1) FireBird cannot make subselects (unfortunatelly)
2) The only solution is make a VIEW like this:
MY_VIEW:
select first 4
PRICE
from BOOKS
where
CUSTOMER='JOEL'
order by
DATE_INC desc
3) Then use this VIEW in select like this:
select AVG(PRICE)
from MY_VIEW
That should do the work..you can combine usage of views to achieve different results
(for example returning average PRICE from VIEW directly and so)
regards,
Kate
ASKER
Ok... Thx again and i think that we will get it.
I put:
========================== ========== ========== ========== =====
MY_VIEW: select first 4 PRICE from BOOKS where CUSTOMER='JOEL' order by DATE_INC desc
========================== ========== ========== ========== =====
But i got the following error message: "Incorrect toke followed by ':'"
I put:
==========================
MY_VIEW: select first 4 PRICE from BOOKS where CUSTOMER='JOEL' order by DATE_INC desc
==========================
But i got the following error message: "Incorrect toke followed by ':'"
Well it was meant like this - create a view MY_VIEW with content:
select first 4 PRICE from BOOKS where CUSTOMER='JOEL' order by DATE_INC desc
;) Kate
select first 4 PRICE from BOOKS where CUSTOMER='JOEL' order by DATE_INC desc
;) Kate
ASKER
Katka, the only problem is: VIEWs doesnt allows the use of "ORDER BY".... :(
Sure in this certain case FireBird is a bit limited.. You'll have to use stored procedure (input parameter is name of customer, output is average value):
CREATE PROCEDURE MY_PROC (
iNAME VARCHAR (32))
RETURNS (
oRESULT FLOAT)
AS
DECLARE VARIABLE vPRICE integer;
DECLARE VARIABLE vCOUNT integer;
BEGIN
oRESULT = 0; vCOUNT = 0;
FOR
select first 4 PRICE
from BOOKS
where CUSTOMER=iNAME
order by DATE_INC desc
into :vPRICE
DO
BEGIN
oRESULT = oRESULT + vPRICE;
vCOUNT = vCOUNT + 1;
END
oRESULT = oRESULT / vCOUNT;
SUSPEND;
END
than execute it like: EXECUTE PROCEDURE MY_PROC ('JOEL') RETURNING_VALUES (your_variable_here)
regards,
Kate
CREATE PROCEDURE MY_PROC (
iNAME VARCHAR (32))
RETURNS (
oRESULT FLOAT)
AS
DECLARE VARIABLE vPRICE integer;
DECLARE VARIABLE vCOUNT integer;
BEGIN
oRESULT = 0; vCOUNT = 0;
FOR
select first 4 PRICE
from BOOKS
where CUSTOMER=iNAME
order by DATE_INC desc
into :vPRICE
DO
BEGIN
oRESULT = oRESULT + vPRICE;
vCOUNT = vCOUNT + 1;
END
oRESULT = oRESULT / vCOUNT;
SUSPEND;
END
than execute it like: EXECUTE PROCEDURE MY_PROC ('JOEL') RETURNING_VALUES (your_variable_here)
regards,
Kate
ASKER
Oh!!! Thx. very much for your insistance!!!
I dont have Delphy right now, but i will test tonight.
Thx. again.
I dont have Delphy right now, but i will test tonight.
Thx. again.
You're welcomed.. if I may, I'll offer you two more advices:
1) make a table CUSTOMERS with fields:
ID - unique identificator [integer]
NAME - name of customer [varchar]
..
and then use an ID field in 'where' condition instead of comparison of varchars, it's much more faster and purer ;)
2) ask questions about Firebird in DB/Interbase forum, you'll get answers much faster and also there's better chance to get some..
regards,
Kate :)
1) make a table CUSTOMERS with fields:
ID - unique identificator [integer]
NAME - name of customer [varchar]
..
and then use an ID field in 'where' condition instead of comparison of varchars, it's much more faster and purer ;)
2) ask questions about Firebird in DB/Interbase forum, you'll get answers much faster and also there's better chance to get some..
regards,
Kate :)
ASKER
Hi Katka,
Our code seems to work but with these two alterations:
========================== ========== =
CREATE PROCEDURE MY_PROC (
iNAME VARCHAR (32))
RETURNS (
oRESULT FLOAT)
AS
DECLARE VARIABLE vPRICE integer;
DECLARE VARIABLE vCOUNT integer;
BEGIN
oRESULT = 0; vCOUNT = 0;
FOR
select first 4 PRICE
from BOOKS
where CUSTOMER='Joel' // If i put "CUSTOMER=iNAME", i got a error message
// I dropped the line "ORDER ..." because it gave a error message
// I dropped the line "into :vPRICE" -> error message
DO
BEGIN
oRESULT = oRESULT + vPRICE;
vCOUNT = vCOUNT + 1;
END
oRESULT = oRESULT / vCOUNT;
SUSPEND;
END
========================== ========== =
I see that you made your best, but it seems that Firebird has some particular details that dont allow us to implement that...
Thx. very much for now, and for your last advices too.
Our code seems to work but with these two alterations:
==========================
CREATE PROCEDURE MY_PROC (
iNAME VARCHAR (32))
RETURNS (
oRESULT FLOAT)
AS
DECLARE VARIABLE vPRICE integer;
DECLARE VARIABLE vCOUNT integer;
BEGIN
oRESULT = 0; vCOUNT = 0;
FOR
select first 4 PRICE
from BOOKS
where CUSTOMER='Joel' // If i put "CUSTOMER=iNAME", i got a error message
// I dropped the line "ORDER ..." because it gave a error message
// I dropped the line "into :vPRICE" -> error message
DO
BEGIN
oRESULT = oRESULT + vPRICE;
vCOUNT = vCOUNT + 1;
END
oRESULT = oRESULT / vCOUNT;
SUSPEND;
END
==========================
I see that you made your best, but it seems that Firebird has some particular details that dont allow us to implement that...
Thx. very much for now, and for your last advices too.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey KATE,
I deeply thank you for your special attention for my question.
Now your recomendation works fine and my problem is gone.
Don´t have words to tell you how i thank you, because that problem seemed doesnt have a solution...
Best regards for you!!!
I deeply thank you for your special attention for my question.
Now your recomendation works fine and my problem is gone.
Don´t have words to tell you how i thank you, because that problem seemed doesnt have a solution...
Best regards for you!!!