Querying a table to get values based on no of digits of a parameter?

Question

Hi,

Considering the following table

I have a large table from which I can query to get the following table

type       no of times type occurs
101            450
102            562
103            245
111            25
112            28
113            21

Now suppose I wanted to get a table which shows me the sum of no of times type occurs for type starting with 1 then starting with 10,11,12,13.......19 then starting with 2, 20,21, 22, 23...29 and so on.

Something like this

1        1331               10        1257
                            11        74
                            12         ..
                            13         ..
                            ..         ..
2        ...                20         ..
                            21         ..

Hope I am clear Thanks

Answer 1

You really have two different queries:

SELECT [type]\100 AS TypePart, Count(t.type) AS CountOftype
FROM t
GROUP BY [type]\100;

And:

SELECT [type]\100 AS TypePart, [type] Mod 100 AS TypeEnd, 
    Count(t.type) AS CountOftype
FROM t
GROUP BY [type]\100, [type] Mod 100;

Where t is the name of the table.

Answer 2

Here on the first query i am getting something like this

utypPart    CountOftype
    1   29
    2   42
    3   46
    4   50
    5   26
    6   45
    7   33
    9   1

it is giving me how many utyp are starting with 1 2 and so on but whai i want is the sum of no of times those types occur for the utyp .