Use of sum command and () in sql

[white605]

Although this statement returns the correct numbers to the report to be completed, Is there a better way to state this statement?

where total_b is the total sale
s4130 and ototal are the taxable items on a contract
the rate of tax is 5%
the discount for prompt payment is 2%

CREATE TABLE test2.dbf (total_b n(10 ,2), s4130 n(10,2), ototal n(10,2))
INSERT INTO test2.dbf (total_b, s4130, ototal) values(1000.00, 500.00, 100.00)
INSERT INTO test2.dbf (total_b, s4130, ototal) values(1000.00, 500.00, 100.00)
INSERT INTO test2.dbf (total_b, s4130, ototal) values(1000.00, 500.00, 100.00)

SELECT ;
	ROUND(SUM(TOTAL_B),2) AS TOTALSALES, ;
	ROUND(SUM(S4130) + SUM(OTOTAL),2) AS TAXABLESALES, ;
	ROUND((SUM(TOTAL_B))-(SUM(S4130)+SUM(OTOTAL)),2) AS DEDUCTION, ;
	ROUND((SUM(S4130)+SUM(OTOTAL))*(.05),2) AS TAXAMOUNT, ;
	ROUND(((SUM(S4130)+SUM(OTOTAL))*(.05))*(.02),2) AS TAXDISCOUNT, ;
	ROUND((((SUM(S4130)+SUM(OTOTAL))*(.05))-(((SUM(S4130)+SUM(OTOTAL))*(.05))*(.02))),2) AS TAXDUE ;
	FROM TEST2

thanks,
wjwjr

 

[bborissov]

I am not sure if this is better, but at least all SUM() are executed once:

CREATE TABLE test2.dbf (total_b n(10 ,2), s4130 n(10,2), ototal n(10,2))
INSERT INTO test2.dbf (total_b, s4130, ototal) values(1000.00, 500.00, 100.00)
INSERT INTO test2.dbf (total_b, s4130, ototal) values(1000.00, 500.00, 100.00)
INSERT INTO test2.dbf (total_b, s4130, ototal) values(1000.00, 500.00, 100.00)

SELECT ;
       TotalSales,;
       (S4130 +oTotal)                    AS TAXABLESALES,;
       (TotalSales-S4130+oTotal)          AS Deduction,;
       ROUND((S4130 +oTotal)*.05,2)       AS TAXAMOUNT,;
       ROUND(((S4130 +oTotal)*.05)*.02,2) AS TAXDISCOUNT,;
       ROUND(((S4130 +oTotal)*.05)-;
            (((S4130 +oTotal)*.05)*.02),2) AS TAXADUE;
    FROM (SELECT SUM(Total_B) AS TotalSales,;
                 SUM(S4130)   AS S4130,;
                 SUM(oTotal)  AS oTotal;
                 FROM TEST2) Tbl1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[white605]

Thanks Borislav,
The From(Select statements throws an error. I suspect it is something to do with sql vs sql{foxpro verison}. I do see your point.
wjwjr
wjwjr

 

[bborissov]

This kind of SELECTs can be done only in VFP9

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Mike Lewis]

White,

Before deciding which code to use (your original code or Borislav's), be sure to do some timing tests on your actual data. The results could vary widely according to the nature of the data and the environment you are running in.

That said, I don't see anything wrong with your original code. It's probably how I would have done it myself.

As Borislav pointed out, his version requres 9.0 or above. If you are seeing an error under 9.0, check the setting of ENGINEBEHAVIOR

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk