Sql Query and Getting Percentages Group Wise

[mstrcmtr]

Select Accna As Grp_Name , MAX(Acccd) As Grp_Code,;
SUM(Qty) AS Item_Qty,SUM(Amt) AS Item_Amt,;
CAST(0 As N(6,2)) As Pctg_Qty,CAST(0 As N(6,2)) As Pctg_Amt ;
GROUP BY 1 ;
ORDER BY 1 ;
INTO CURSOR Grp_AcWs ;
READWRITE

Q. What is the way of getting Quantity and Amount Percentages of every Line ON Group Wise total instead of Grand Total of Qty and Amount

 

[Olaf Doschke]

The percentage in comparison with the grand total of qty and amount? Well, that first needs these two figures for reference as 100%.

So that's always a two steps process. When the grouping mechanism only knows the group it aggregates, it can't compute the percentage you want at the same time.

That's why this code creates a readwrite cursor and I guess the next code then fills in the gaps, right?
Stay with it, it doesn't get better.

CREATE CURSOR crsSales (iQty I, yAmt Y, iAcct I)
FOR i = 1 TO 99998
   INSERT INTO crsSales VALUES (RAND()*5, RAND()*10000*$0.01, i/100)
ENDFOR i

t0 = SECONDS()

SELECT SUM(iQty) iTotalQty, SUM(yAmt) as yTotalAmt;
   FROM crsSales;
   INTO CURSOR crsTotals

t1 = Seconds()

SELECT iAcct,SUM(iQty) AS Item_Qty,SUM(yAmt) AS Item_Amt,;
CAST(100*SUM(iQty) / crsTotals.iTotalQty AS N(6,2)) As Pctg_Qty,;
CAST(100*SUM(yAmt) / crsTotals.yTotalAmt As N(6,2)) As Pctg_Amt ;
FROM crsSales ;
GROUP BY iAcct ;
ORDER BY iAcct ;
INTO CURSOR Grp_AcWs

t2 = SECONDS()

? t1-t0
? t2-t1

This takes split seconds without even being indexed. OK, it's neither stored and read from disk, nor from a network share, these two things will differ in your real scenario and will likely take the most time.

Is your dataset that huge, that you'd like to accelerate it? You could sum daily, weekly, monthly,... and store these figures in a reliable way (eg cryptographically signed), so building longer-term totals you can rely on the values you already summed. On the other side, you can sum up a million records very fast, what is your problem, really? Did you not index your data?

Bye, Olaf.