Want to Calculate Percentage With in Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Want to Calculate Percentage With in Columns

Author

Topic

librananurag
Starting Member

3 Posts

Posted - 2011-11-18 : 08:58:24

Hi Guys,

I am new to SQL/Sybase programming. I am having a Sybase table which has five fields UserName, OrderDate, Location, Qty. Now I want the Pecentage of Data for LAST MONTH whose location is AES with the total data sent by each user. That means I need %=QtyForAESLocation/TOtalQty*100. I am using following query which gives me arithmatic exception. pelase help me out. i need it bit urgent....

User Loc OrderDate Qty
A AES 11/4/2011 100
A MER 11/4/2011 500
B MER 11/4/2011 500
C AES 11/4/2011 500
A DIV 11/8/2011 500
A DIV 11/9/2011 100
D AES 11/10/2011 100
E PIB 11/4/2011 100
A AES 11/8/2011 100
B AXS 11/10/2011 100
D AES 11/9/2011 100

Result Need should be something like this---
User %AES Remaining % OrderDate
A 45 55 11/4/2011
B 25 75 11/4/2011
C 50 50 11/4/2011
D 100 0 11/4/2011
A 25 75 12/4/2011
D 60 40 12/4/2011

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-18 : 10:04:08

This is a MS SQL Server forum only hence you can get better help in a sybase forum...

No, you're never too old to Yak'n'Roll if you're too young to die.

librananurag
Starting Member

3 Posts

Posted - 2011-11-21 : 03:19:35

But query should be asame in SQl as well...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 03:27:24

quote:
Originally posted by librananurag

But query should be asame in SQl as well...

Nope syntaxes are not same in Sybase and in MS SQL Server

in sql server we do this like below . you can try that. but very little chance it will work in Sybase


SELECT User,
SUM(CASE WHEN Loc='AES' THEN Qty ELSE 0 END)*100.0/NULLIF(SUM(Qty),0) AS [%AES],
SUM(CASE WHEN Loc<>'AES' THEN Qty ELSE 0 END)*100.0/NULLIF(SUM(Qty),0) AS [Remaining%],
OrderDate
FROM table
GROUP BY User,OrderDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

librananurag
Starting Member

3 Posts

Posted - 2011-11-21 : 05:46:15

Awesom dude.... You rocks..it worked for me with few corrections..... thanksssssss alottttttttt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 05:54:29

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources