Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How i implement a SQL instruction that makes this.

Posted on 2004-08-15
20
Medium Priority
?
160 Views
Last Modified: 2010-04-05
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.
0
Comment
Question by:joelsilva
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 4
20 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 11805915
SELECT ... AVG(PRICE) ...
0
 

Author Comment

by:joelsilva
ID: 11806398
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?
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 11807134
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:joelsilva
ID: 11807333
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?



0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 11807487
try with

SELECT FIRST 4 AVG(PRICE)
FROM books
WHERE customer='JOEL'
ORDER BY DATE_INC DESC
0
 

Author Comment

by:joelsilva
ID: 11807570
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.
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 11807615
If your DB implementation support nested selects, you can try this:

SELECT AVG(SUBSELECT.SUBSELECT_PRICE)
FROM
  ( SELECT FIRST 4 PRICE AS SUBSELECT_PRICE
    FROM BOOKS
    WHERE CUSTOMER='JOEL'
    ORDER BY DATE_INC DESC ) SUBSELECT
0
 

Author Comment

by:joelsilva
ID: 11810347
No way man. I am using Firebird 1.5.
I deeply thank you by your insistance.
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 11818308
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
0
 

Author Comment

by:joelsilva
ID: 11818328
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_PRICE)
FROM
  ( SELECT FIRST 4 PRICE AS SUBSELECT_PRICE
    FROM BOOKS
    WHERE CUSTOMER='JOEL'
    ORDER BY DATE_INC DESC ) SUBSELECT
=================================

But it didnt worked.
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 11822727
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
0
 

Author Comment

by:joelsilva
ID: 11836667
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 ':'"

0
 
LVL 10

Expert Comment

by:_Katka_
ID: 11836928
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
0
 

Author Comment

by:joelsilva
ID: 11838141
Katka, the only problem is: VIEWs doesnt allows the use of "ORDER BY".... :(
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 11840681
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
0
 

Author Comment

by:joelsilva
ID: 11841227
Oh!!! Thx. very much for your insistance!!!

I dont have Delphy right now, but i will test tonight.

Thx. again.

0
 
LVL 10

Expert Comment

by:_Katka_
ID: 11842965
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 :)
0
 

Author Comment

by:joelsilva
ID: 11848997
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.
0
 
LVL 10

Accepted Solution

by:
_Katka_ earned 60 total points
ID: 11851326
Hi,

 that stored procedure was running fine on FireBird 1.5.1, maybe the problem with iNAME was that it's to be :iNAME ;) otherwise it functions well in form:

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 CUSTOMER
     where CUSTOMER=:INAME
     order by DATE_INC desc
     into :vPRICE
   DO
    BEGIN
      oRESULT = oRESULT + vPRICE;
      vCOUNT = vCOUNT + 1;
    END
  IF (vCOUNT=0) THEN
  BEGIN
    oRESULT = 0;
  END ELSE
  BEGIN
    oRESULT = oRESULT / vCOUNT;
  END
  SUSPEND;
END

I also added 'ZERO DIVISION' check..for completeness..

regards,
Kate
0
 

Author Comment

by:joelsilva
ID: 11867651
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!!!


0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question