Jump to content

Better Sql Statement


jobarb
 Share

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 

Link to comment
Share on other sites

  • 3 weeks later...
  • 4 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...