Group/AVG - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Group/AVG

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-09-14 : 10:02:37

I have the following:

select CLIENT_CODE,count(TICKER) as [# of Securities] from POS
group by CLIENT_CODE
order by CLIENT_CODE

Give me results like the following


CLIENT_CODE     # OF SECURITIES
110903010      	17
110909017      	1
110911013      	4
110913019      	2
11A204012      	1
11B034012      	2
11C046015      	1
11C048011      	1
11D412018      	4
11D413016      	4
11D414014      	5
11G041012      	23
11G041020      	91
11J078011      	1
11J079019      	1
11J080017      	2
11L065015      	3
11M265010      	63
11M265028      	2
11M266018      	6
11M267016      	14
11M268014      	1
11M269012      	2
11M270010      	3
11M272016      	9
11ML11111      	127
11ML22222      	134

I would like to get the average number of securites per CLIENT_CODE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:09:06

[code]select CLIENT_CODE,
count(TICKER) as [# of Securities]
from POS
group by CLIENT_CODE

UNION ALL

SELECT 'Total',
AVG(TICKER)
FROM POS

order by 1[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:12:13

[code]DECLARE @Sample TABLE (Code VARCHAR(9), Ticker INT)

INSERT @Sample
SELECT '110911013', 2 UNION ALL
SELECT '110911013', 2 UNION ALL
SELECT '110913019', 2 UNION ALL
SELECT '11A204012', 1 UNION ALL
SELECT '11B034012', 2 UNION ALL
SELECT '11C046015', 1 UNION ALL
SELECT '11C048011', 1 UNION ALL
SELECT '11D412018', 1 UNION ALL
SELECT '11D412018', 1 UNION ALL
SELECT '11D412018', 1 UNION ALL
SELECT '11D412018', 1 UNION ALL
SELECT '11D413016', 4

SELECT Code,
COUNT(*) AS Securities,
AVG(Ticker) AS Yak
FROM @Sample
GROUP BY Code
ORDER BY Code[/code]

E 12°55'05.25"
N 56°04'39.16"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-09-14 : 10:25:32

Trying to be a big boy and figure some things out on my own, but when i run it I get
"The average aggregate operation cannot take a char data type as an argument"
the column TICKER is a CHAR(9)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:32:49

I see.
Did you really think we could expect that behaviour when yuo have given us nothing to know about your table?
Please remember, this is the first time we ever see your table!

What kind of data do TICKER column hold?
And also, how do you expect and string to be calculated as an average?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:33:46

Try this

SELECT		'Total',
		1.0 * COUNT(TICKER) / COUNT(DISTINCT CLIENT_CODE)
FROM		POS

E 12°55'05.25"
N 56°04'39.16"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-09-14 : 10:36:07

My apologizes, the end result I want is to find out the average number of tickers per account. A sampling of the ticker column is as follows:
000752AA3
000752AA3
000752AA3
000886AB7
00180TFF0
001957BD0
00209TAA3
00252FAP6
002824AJ9
002824AK6

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:37:18

And what is the AVERAGE for "00180TFF0" and "001957BD0" ??????????????????????????????????????????

E 12°55'05.25"
N 56°04'39.16"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-09-14 : 10:37:32

Thanks, I believe that worked, your hard work is appreciated. In posting more and more on this forum, I am beginning to learn that I need to offer up more data/example and am trying.

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-09-14 : 10:40:31

I should of said average number of total TICKERS / per account. So count of all the tickers per account and than give me an average of the total per account

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:42:44

And again, AVERAGE OF WHAT?
By default, an average is the sum of something divided by the number of samples producing the sum.

Please post some proper and accurate sample data and your expected output.
Also read this blog, please!
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 10:43:54

[code]select CLIENT_CODE,
count(TICKER) as [# of Securities],
1.0 * COUNT(TICKER) / COUNT(DISTINCT TICKER) AS Twig
from POS
group by CLIENT_CODE
order by CLIENT_CODE[/code]

E 12°55'05.25"
N 56°04'39.16"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-09-14 : 10:55:20

PESO, I appreciate your patience once again, and appreciate the advice offered up via that blog. In doing

SELECT		'Total',
		1.0 * COUNT(TICKER) / COUNT(DISTINCT CLIENT_CODE)
FROM		POS

It's giving me the number I want. It's totalling up the total number of TICKER'S and dividing it up by the total number of accounts giving me the average number of tickers/account. Once again, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources