Jump to content
jobarb

Better Sql Statement

Recommended Posts

Hi. 

I am new to this forum .

I have the following sql select statement 

 select SUM(QTY), DESCRIPTION, PRICE ,SALESTAX,COSTPRICE, SUM(QTY*PRICE) as subtotal,
 SUM(QTY*PRICE) * SALESTAX / (SALESTAX+100) as vat,
   SUM(QTY*PRICE) - SUM(QTY*PRICE) * SALESTAX / (SALESTAX+100) as SELLINGPRICEEXVAT ,
    SUM(QTY*COSTPRICE) as totalcost,
    round(((SUM(QTY*PRICE))- (SUM(QTY*PRICE) * SALESTAX / (SALESTAX+100)) - SUM(QTY*COSTPRICE)),2) as GP,
      ((((SUM(QTY*PRICE))- SUM(QTY*PRICE) * SALESTAX / (SALESTAX+100)) - SUM(QTY*COSTPRICE))
       / (SUM(QTY*PRICE)- SUM(QTY*PRICE) * SALESTAX / (SALESTAX+100)))* 100 as perc
 from SALESITEMSHISTORY
  where (Qty >0 and price > 0)
   group by MENUFAMILY,QTY,DESCRIPTION,PRICE,SALESTAX,COSTPRICE
 

can any one please inform me if it's possible to have a better or more efficient way to write this.

executed on a table with 780642 records takes about 6.24 secs  on localhost core i7 laptop, might take long on remote server from a client with a weaker pc.

Using win10  with firebird db 

Thank you

Best regards 

Joao 

Share this post


Link to post
Share on other sites

There's always a better way.  What are you trying to accomplish?

Share this post


Link to post
Share on other sites

Thank you  for our reply, Sorry for the long delay in responding, this covit thing .....

What i am trying to accomplish is a gross profit report based on sale items sold for a given period

Kind  Regards

Jo

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...