select * group by part of a field

[Mack1979]

I have a standard (in that I can't change it) output file from a system. Unique rows are identified by the "group" and "time" columns, examples of these are: Iteration 1|ASL|NWPF|2_01, Iteration 1|ASL|NWPF|2_02 and time is just 0-480. I want to merge this output down (removing the final 01/02 split in the example group above) which with vfp7 I could easily do with select * and group by group, time even if with my limited knowledge I had to do with a cusror and then overwriting the group field to the unique part). I don't want to list every field (there are about 4 more text fields but they're the same on every record and around 150 numeric fields but these could change and I don't want to update this grouping code every time the output changes because the odds are someone would forget and it would break.

Thanks for the help!

 

[Olaf Doschke]

With VFP7 you are not forced to specify all fields you don't aggregate in the group by clause.
With SET ENGINEBEHAVIOR 70 you can use the same faulty or let's say lazy group by in VFP8 or 9, too.

It's not hard to get the full list of fields, thoug, FIELD(N) with N from 1 TO FCOUNT(), AFIELDS to name just two helpful function for retrieving field names.

If I understand you correct you cen get the grouped result by SELECT * FROM table GROUP BY GROUP, TIME.

I can't follow this logic, because I don't know what part of your sample data would be the group column. 2_01 and 2_02 would not sum up as group 2, those would be two seperate groups. You'd need to query LEFT(group,1) as maingroup to be able to group by just the main group number 2.

You also give an example of a time 0-480, which is not find in record1 or 2 of your sample data, so at least I am lost in your description, not getting your main point about what you want done in a fashion you don't need to change any more.

Could you please clarify further?

Bye, Olaf.

 

[Mike Lewis]

Welcome to the forum.

I've read your post three times, but I'm still not sure what your question is. However, based on the title of your post, I'm guessing you want to group on a substring, such as the first three characters of a field. In other words, you want to do something this:

SELECT .... GROUP BY LEFT(SomeField, 3)

If that's what you're asking, the answer is No; you can't do that in VFP. You can only group on an entire field, or on mutliple fields.

What you can do is create an intermediate cursor, containing all the fields you want in the final result set, plus the substring in question. You can then do a separate SELECT, grouping on that new field.

Something like this:

SELECT Field1, LEFT(SomeField, 3) AS GroupField FROM ... INTO CURSOR csrTemp

SELECT COUNT(Field1), GroupField FROM csrTemp GROUP BY GroupField

Obviously, I'm using a LEFT() function here only as an example. The above applies whatever expression you want to group on.

My apologies if this doesn't answer your question. In that case, perhaps you could explain a bit more clearly what you wish to achieve.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[awesomeheero17]

let me try based on how i understand your problem , i'v tried make a new field consisting a value matches your category of gourping. the value group record concatinated with a part value of time field. and gruop them to that field. But it doesn't sum up the fields with numeric values.

select fld_group, fld_time, (alltrim(fld_group)+alltrim(left(fld_time,2)) as NewGrp, * from myDbf group by NewGrp


but if i want to sum up those numeric fields without specifying each fileds with the same gouping that you like im using vfp codes. like this


select 1
use myDbf
index on (alltrim(fld_group)+alltrim(left(fld_time,2)) to NewGrp
total to tmpmyDBF on (alltrim(fld_group)+alltrim(left(fld_time,2))

all numeric fields will sum up
my desired result would be in tmpmyDBF.

hope i got what you wanted to say,
kudos

 

[Mack1979]

Sorry for being really unclear people! awesomeheero17 did manage to make sense of it and that's done the trick. For future reference I have fields:
group = Iteration 1|ASL|2_01, Iteration 1|ASL|2_02...Iteration 1|ASL|2_27 (I have 2 iterations in this file and the 2_ can also be 1_)
time = 0, 1,..., 12, 24, ..., 480
and many other numeric fields I wish to sum so that the total number of rows is 27 (subgroups to combine) x 2 (iterations) x 2 (primary groups) x 52 time periods = 5616 rows. My final code is something like this:


USE (filename)
INDEX ON (LEFT(ALLTRIM(group),LEN(ALLTRIM(group))-5) + ALLTRIM(time)) TO NewGrp
total TO tmpmyDBF ON (LEFT(ALLTRIM(group),LEN(ALLTRIM(group))-5) + ALLTRIM(time))
use tmpmdDBF
replace ALL group WITH LEFT(ALLTRIM(group),LEN(ALLTRIM(group))-5)

which gives me a file tmpmyDBF.dbf where the groups are Iteration 1|ASL|1, Iteration 1|ASL|2, Iteration 2|ASL|1, Iteration 2|ASL|2 and the time periods remain 0, 1,..., 12, 24, ..., 480 giving me 208 rows where all the numeric fields are summed

Thanks all for the help!