jobarb Posted July 4, 2020 Share Posted July 4, 2020 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 More sharing options...
niche Posted July 22, 2020 Share Posted July 22, 2020 There's always a better way. What are you trying to accomplish? Link to comment Share on other sites More sharing options...
jobarb Posted August 13, 2020 Author Share Posted August 13, 2020 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 Link to comment Share on other sites More sharing options...
niche Posted August 15, 2020 Share Posted August 15, 2020 what columns do you want to target? Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now