query to sort nos

[cliff]

i have the following data

cdate nos total of digits(int+mod of nos)
24/7/2008 12 3
24/7/2008 16 7
24/7/2008 23 5
24/7/2008 35 8
24/7/2008 42 6
25/7/2008 16 7
25/7/2008 25 7
25/7/2008 32 5
25/7/2008 43 7
26/7/2008 04 4
26/7/2008 13 4
26/7/2008 37 10
26/7/2008 49 13

basically I want to group total of digits of above nos into different
groups like to
1to3, 4 to 6,7 to 9, 10 to 13 etc and result should something like :-
total of digits
cdate 1to 3 4to6 7to9 10to 13
24/7/2008 1 2 2 0
25/7/2008 0 1 3 0
26/7/2008 0 2 0 2

further I want to put digits total into different group like
1,2,10,11,12,13 into one group, 4,5,9 into second group, 6,7,8 into last
group and result like :-

cdate group I group 2 group last
24/7/2008 1 1 2
25/7/2008 0 1 3
26/7/2008 2 2 0

Kindly help me to solve both problems

cliffs

 

[KARL DEWEY]

This will do your first requirement --
TRANSFORM Count(cliff.[total of digits]) AS [CountOftotal of digits]
SELECT cliff.[cdate]
FROM cliff
GROUP BY cliff.[cdate]
PIVOT Partition([total of digits],1,13,3);

For the second I see no rationale for the grouping so you need to create a
translation table like this --
1 1
2 1
10 1
11 1
12 1
13 1
4 2
5 2
9 2
6 3
7 3
8 3

 

[KARL DEWEY]

After building the translation table use this query --
TRANSFORM Count(cliff.nos) AS CountOfnos
SELECT cliff.cdate
FROM cliff INNER JOIN [Translation] ON cliff.nos = Translation.[Value 1]
GROUP BY cliff.cdate
PIVOT Translation.[Data 1];

--
KARL DEWEY
Build a little - Test a little

This will do your first requirement --
TRANSFORM Count(cliff.[total of digits]) AS [CountOftotal of digits]
SELECT cliff.[cdate]
FROM cliff
GROUP BY cliff.[cdate]
PIVOT Partition([total of digits],1,13,3);

For the second I see no rationale for the grouping so you need to create a
translation table like this --
1 1
2 1
10 1
11 1
12 1
13 1
4 2
5 2
9 2
6 3
7 3
8 3

--
KARL DEWEY
Build a little - Test a little

i have the following data

cdate nos total of digits(int+mod of nos)
24/7/2008 12 3
24/7/2008 16 7
24/7/2008 23 5
24/7/2008 35 8
24/7/2008 42 6
25/7/2008 16 7
25/7/2008 25 7
25/7/2008 32 5
25/7/2008 43 7
26/7/2008 04 4
26/7/2008 13 4
26/7/2008 37 10
26/7/2008 49 13

basically I want to group total of digits of above nos into different
groups like to
1to3, 4 to 6,7 to 9, 10 to 13 etc and result should something like :-
total of digits
cdate 1to 3 4to6 7to9 10to 13
24/7/2008 1 2 2 0
25/7/2008 0 1 3 0
26/7/2008 0 2 0 2

further I want to put digits total into different group like
1,2,10,11,12,13 into one group, 4,5,9 into second group, 6,7,8 into last
group and result like :-

cdate group I group 2 group last
24/7/2008 1 1 2
25/7/2008 0 1 3
26/7/2008 2 2 0

Kindly help me to solve both problems

cliffs