×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Sql Query and Getting Percentages Group Wise

Sql Query and Getting Percentages Group Wise

Sql Query and Getting Percentages Group Wise

(OP)
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

RE: Sql Query and Getting Percentages Group Wise

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.

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close