Creating Counts

Question

Creating Counts

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
July 27, 2010 at 3:22 pm

#224636

Hello Everyone
I am just fooling around trying to better myself. How can I complete this count puzzle, without a tally table? Not sure that a tally table would be of use, I cannot figure that part out. That is why I am asking for help. I would rather not use a cursor either. I have a lot of problems even typing that "curs" word LOL
DECLARE @Visitor TABLE
(
VisitorCount int
)
INSERT INTO @Visitor
(
VisitorCount
)
VALUES
(
34
)
INSERT INTO @Visitor
(
VisitorCount
)
VALUES
(
56
)
INSERT INTO @Visitor
(
VisitorCount
)
VALUES
(
45
)
INSERT INTO @Visitor
(
VisitorCount
)
VALUES
(
56
)
INSERT INTO @Visitor
(
VisitorCount
)
VALUES
(
11
)
INSERT INTO @Visitor
(
VisitorCount
)
VALUES
(
56
)
INSERT INTO @Visitor
(
VisitorCount
)
VALUES
(
45
)
INSERT INTO @Visitor
(
VisitorCount
)
VALUES
(
11
)
INSERT INTO @Visitor
(
VisitorCount
)
VALUES
(
72
)
SELECT * FROM @Visitor
ORDER BY VisitorCount ASC
[\code]
Give me this result:
VisitorCount
11
11
34
45
45
56
56
56
72
I would like to count the number of times, and increment each, for each of the VisitorCounts.
How would I create another query to give me these results?
111
112
341
451
452
561
562
563
721
Thanks in Advance for your help.
Andrew SQLDBA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

Thanks Cleko

I knew about the Insert statement, that is nice. I simply created one and then copied and pasted the others. I do use it in my production code, and am changing my other code over.

Thanks for your help. I am looking into the methods that you used, that was very impressive.

Andrew SQLDBA

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

You can also just use

SELECT visitor_cnt,

ROW_NUMBER() OVER (PARTITION BY visitor_cnt ORDER BY visitor_cnt) AS grp_seq_nbr

FROM @Visitors

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]