How to store TSQL query results for later display

Question

I've never used TSQL before, but I decided I wanted to poke around in the SO data dump anyways. So this is probably a rookie question. I did try to search for an answer, but I don't know the technical term for what I'm trying to do, and search is only so good if you don't have the right keywords.

My goal is to find out how many questions have a score of 0, how many have a score of 1, how many have a score of 2, &c. I can write a query to find out those individual counts, no problem. I'd like to be able to run a query containing a loop, though. Here's what I'm using so far:

DECLARE @counter int
SET @counter = 0
WHILE @counter < 3
BEGIN
    SELECT
        COUNT(*)
    FROM
        Posts
    WHERE
        PostTypeId = 1
    AND
        Score = @counter

    SET @counter = @counter + 1
END

(EDIT: the eventual goal is to expand from [0, 3] to [-100, 1000] or whatever is necessary to hit all existing questions) Obviously, my problem is that I'm grabbing the count in each cycle of the loop and then not doing anything with it. Currently, the result I get is just the count of whatever @counter is set to initially; the number of iterations is irrelevant. Is it possible to store the results somewhere and then display as something like:

+-------+--------------------------------+
| Score | NumberOfQuestionsWithThatScore |
+-------+--------------------------------+
|   -10 |                           111  |
+-------+--------------------------------+
|    -9 |                             0  |
+-------+--------------------------------+
|    -8 |                           248  |
+-------+--------------------------------+
|   ... |                           ...  |
+-------+--------------------------------+

(EDIT: any reasonably clear display is fine, the above table is just a sample; EDIT 2: modified the design of the table to clear up continuing confusion)

If so, what is this called, and how is it done?

Answer 1

Actually you can do this in a single pass...

SELECT COUNT(*) AS Total, MAX(Score) AS Score
FROM Posts
WHERE PostTypeId = 1 And Score <= 3
Group By Score

That should give you a nice table like:

Score   Total
0       2490
1       2904
2       2110

Sore thats off the cuff, not in front of a DB right now to verify the syntax. Look up "Group By" to get a better idea.

AS @OMG Ponies points out this is not pivot'ed as you originally had. If you want to have a single column, you would need to use SELECT and SUM to do that.