Query for Count and total/uniqueID

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query for Count and total/uniqueID

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-03-22 : 12:42:15

I hava table with acctno and charge, i would like to have a column with charge/Acctno, so that i can average the charge/Acctno.

AcctNo Charge Charge/AcctNo
123 100 100
124 50 50
125 200 200

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 12:45:59

[code]SELECT AcctNo,Charge,Charge/COUNT(AcctNo)OVER(PARTITION BY Charge) AS [charge/Acctno] FROM YourTable[/code]

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

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-03-22 : 12:55:26

Thanks a lot Visakh.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 12:56:12

welcome

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-22 : 20:37:57

quote:

Originally posted by visakh16

SELECT AcctNo,Charge,Charge/COUNT(AcctNo)OVER(PARTITION BY Charge) AS [charge/Acctno] FROM YourTable

I'm not sure this is what sreenu9f is after. The output of the [charge/Acctno] column here has nothing to do with the average charge per AcctNo. Also, if the average charge per AcctNo is in the output, then only one row per AcctNo should be returned, and obviously the charge column should be omitted. Something like this:

SELECT AcctNo, AVG(charge) as [Charge/AcctNo]
FROM YourTable
GROUP BY AcctNo

Check out the following comparison. The figures in the original [Charge/AcctNo] are virtually meaningless.

DECLARE @tab TABLE (
	AcctNo INT,
	Charge Money
)

Insert into @tab
SELECT 1, 100
UNION ALL SELECT 2, 200
UNION ALL SELECT 3, 300
UNION ALL SELECT 1, 300
UNION ALL SELECT 1, 200
UNION ALL SELECT 4, 300
UNION ALL SELECT 5, 300
UNION ALL SELECT 6, 300
UNION ALL SELECT 6, 300

SELECT AcctNo, AVG(charge) as [Charge/AcctNo]
FROM @tab
GROUP BY AcctNo

SELECT AcctNo, Charge, Charge/COUNT(AcctNo)OVER(PARTITION BY Charge) AS [charge/Acctno] FROM @tab

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources