Grouping query - help requested 1

[VickyC]

hello to all

Here is a sample from a table... (* sorry, but I can't get the TGML to show the tables properly??? I'm using tt and /tt in square brackets)

[tt]
Row DataVal
1 7
2 2
3 8
4 -2
5 -3
6 -4
7 -8
8 4
9 5
10 -6
11 -7
12 3
13 6
14 -5[/tt]

I need help writing a query to a) Group DataVal by SIGN (there are no values = 0), then b) COUNT the number of records in each group.
The output should show the 1st row numberof each group, and the COUNT of each group. Here's what the output would look like...

[tt]
Grp1stRow GrpCount
1 3
4 4
8 2
10 2
12 2
14 1
[/tt]

Thanks for any clues
Vicky

 

[SkipVought]

Hi,

Okay, here's your example copied from an Excel sheet and pasted here, with Pre TGML rather than TT...
[pre]
Row DataVal
1 7
2 2
3 8
4 -2
5 -3
6 -4
7 -8
8 4
9 5
10 -6
11 -7
12 3
13 6
14 -5
[/pre]

Worked on a pure SQL solution and failed. So this is a spreadsheet/SQL solution...

1) augmented the source table in Excel with formulas...
[pre]
Row DataVal sgn toggle
1 7 1 1
2 2 1 1
3 8 1 1
4 -2 0 2
5 -3 0 2
6 -4 0 2
7 -8 0 2
8 4 1 3
9 5 1 3
10 -6 0 4
11 -7 0 4
12 3 1 5
13 6 1 5
14 -5 0 6
[/pre]
the sgn column formula
[tt]
C2: =IF(B2>0,1,0)
[/tt]
the toggle column formula
[tt]
D2: =IF(C2=C1,D1,IF(ISNUMBER(D1),D1,0)+1)
[/tt]

2) The SQL in Excel via Data > Get External Data > From Other Source > MS Query ... drill down to my workbook/worksheet named Source...

[pre]
SELECT min(src.Row) as [Grp1stRow], count(src.DataVal) as [GrpCount]

FROM `C:\Users\Skip\Documents\tt-SignGroup.xlsx`.`Source$` src

Group By src.toggle
[/pre]
...and my results
[pre]
Grp1stRow GrpCount
1 3
4 4
8 2
10 2
12 2
14 1
[/pre]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}