×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*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.

# 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 ;

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;
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 ;
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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!