counting top 5 for each unique number

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)

counting top 5 for each unique number

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-04-14 : 09:23:51

i have a table if diffrent numbers.
each number can appear X>=0 times.
i want to count each number maximi of 5 times (or less if there are lest times of that number)
thnaks in advance
pelg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 09:29:42

Can you make your reqmnt clear with some sample data and your expected o/p out of that data sample?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-14 : 09:29:47

[code]select number, case when count(number) > 5 then 5 else count(number) end from table group by number[/code]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-04-14 : 10:37:36

RyanRandall - thanks its almost there
i will describe more the table
number | txt
================
33 x
33 x
33 x
33 y
33 x
33 x
11 g
11 g
11 t
11 t
11 t
11 t
11 t
11 t

i want to get the total for each of the letters in the txt column.
where - for each "number column" i will count only the 5 first values that i got from him - the rest are not intresting me.
how should i fix your query
thnaks
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 10:44:33

Define what is "first" to you.
Do you have some column or other logic defining "order" in your table.
Or any 5 records randomly for each txt will do for summation?

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

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 10:45:27

>> i will count only the 5 first values that i got from him
How do you know the first 5 values - there's nothing in that table to order the entries.
Are the values always the same? You want the total but only 5 if there are more than 5?

select txt, tot = number * case when num > 5 then 5 else num end
from
(select txt, number = max(number), num = count(*)
from tbl
group by txt) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 10:49:57

[code]DECLARE @Sample TABLE (Number INT, txt CHAR(1))

INSERT @Sample
SELECT 33, 'x' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 33, 'y' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 11, 'g' UNION ALL
SELECT 11, 'g' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't'

SELECT DISTINCT s1.txt,
(SELECT SUM(Number) FROM (SELECT TOP 5 s2.Number FROM @Sample AS s2 WHERE s2.txt = s1.txt ORDER BY s2.Number DESC) AS d)
FROM @Sample AS s1[/code]

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

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-04-14 : 10:51:33

ok i wil lexplain
the result should be :
33 count(x)=4
33 count(y)=1
11 count(g)=2
11 count(t)=3
i hope this more clear?
should i add an AUTOINCEMENT column to the table?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 11:01:07

>> should i add an AUTOINCEMENT column to the table?
Yes - otherwise there's no way of getting the first 5.

select t.Number, t.txt, count(*)
from tbl t
join (select Number, seq, cnt = (select count(*) from tbl t2 where t2.Number = t1.Number and t2.seq <= t1.seq) from tbl t1) t3
on t.seq = t3.seq
where t3.cnt <= 5
group by t.Number, t.txt

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-04-14 : 11:01:30

Peso - how can i make your query to count the data by the order of insert to the db?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 15:18:04

quote:
Originally posted by pelegk2

ok i wil lexplain
the result should be :
33 count(x)=4
33 count(y)=1
11 count(g)=2
11 count(t)=3
i hope this more clear?

No. In your sample data, you have 5 txt "x" and 6 txt "t".

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 15:20:06

quote:
Originally posted by pelegk2

Peso - how can i make your query to count the data by the order of insert to the db?


SELECT DISTINCT	s1.txt,
		(SELECT SUM(Number) FROM (SELECT TOP 5 s2.Number FROM @Sample AS s2 WHERE s2.txt = s1.txt ORDER BY s2.IdentityCol DESC) AS d)
FROM		@Sample AS s1

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

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 15:28:39

quote:
Originally posted by Peso

No. In your sample data, you have 5 txt "x" and 6 txt "t".

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

It's beacuse the data is ordered.
If you take the first 5 entries for 33 you get 4 x's and 1 t.
Have a look at my query which assumes an identity column seq.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 15:38:50

Oh, I see.
So what OP wants is "first found" running streak for each txt?

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

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-04-14 : 15:59:08

Execly! i want whats first found! an by that i count!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-14 : 16:09:52

[code]-- Data
DECLARE @Sample TABLE (i int identity(1, 1), Number INT, txt CHAR(1))

INSERT @Sample
SELECT 33, 'x' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 33, 'y' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 11, 'g' UNION ALL
SELECT 11, 'g' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't'

-- Calculation
select Number, txt, count(*) as cnt from @Sample a
where (select count(*) from @Sample where Number = a.Number and i < a.i) < 5
group by Number, txt

/* Results
Number txt cnt
----------- ---- -----------
11 g 2
11 t 3
33 x 4
33 y 1
*/[/code]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 16:16:45

I think count for x should be 3, and count for t should be 5.

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

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 16:20:16

Nope - the first 5 for 33 are

x, x, x, y, x
4 x's and 1 y.

try
DECLARE @Sample TABLE (Number INT, txt CHAR(1), seq int identity)

INSERT @Sample
SELECT 33, 'x' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 33, 'y' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 33, 'x' UNION ALL
SELECT 11, 'g' UNION ALL
SELECT 11, 'g' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't' UNION ALL
SELECT 11, 't'

select t.Number, t.txt, count(*)
from @Sample t
join (select Number, seq, cnt = (select count(*) from @Sample t2 where t2.Number = t1.Number and t2.seq <= t1.seq) from @Sample t1) t3
on t.seq = t3.seq
where t3.cnt <= 5
group by t.Number, t.txt

Number txt
----------- ---- -----------
11 g 2
11 t 3
33 x 4
33 y 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 16:37:51

I get it now. It is not a "running streak".
The result should be the same as

DECLARE	@Sample TABLE (RowID INT, Number INT, txt CHAR(1))

INSERT	@Sample
SELECT	1, 33, 'x' UNION ALL
SELECT	2, 33, 'x' UNION ALL
SELECT	3, 33, 'x' UNION ALL
SELECT	4, 33, 'y' UNION ALL
SELECT	5, 33, 'x' UNION ALL
SELECT	6, 33, 'x' UNION ALL
SELECT	8, 11, 'g' UNION ALL
SELECT	10, 11, 'g' UNION ALL
SELECT	12, 11, 't' UNION ALL
SELECT	13, 11, 't' UNION ALL
SELECT	20, 11, 't' UNION ALL
SELECT	21, 11, 't' UNION ALL
SELECT	22, 11, 't' UNION ALL
SELECT	23, 11, 't'

-- Calculation
SELECT		s.Number,
		y.txt,
		COUNT(*) AS Occurencies
FROM		(
			SELECT		Number
			FROM		@Sample
			GROUP BY	Number
		) AS s
CROSS APPLY	(
			SELECT TOP 5	t.txt,
					t.RowID
			FROM		@Sample AS t
			WHERE		t.Number = s.Number
			ORDER BY	t.RowID
		) AS y
GROUP BY	s.Number,
		y.txt

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

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-04-14 : 17:26:24

thnaks rn and PESO - the solution i think is not so obvious of itself.
can you exlpain more about what you have done?
thnaks
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-14 : 17:31:56

I get no credit?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources