Subscribe to this thread
Home - General / All posts - M8 Query INSERT INTO and GROUP BY
ColinD


1,818 post(s)
#11-Nov-17 23:58

There is a [SOURCE] table with columns [Site_id], [Value], [Group], and there are six groups G1 to G6.

There is also a [TARGET] table with columns [countG1] to [countG6] and [sumG1] to [sumG6].

My pseudo code, snippets of which work is

select count(*) [Group] from [SOURCE] where [Group] like "%G1%"..."%G6%" insert into [TARGET].[countG1]....[countG6] group by [SOURCE].[Site_id]

select sum ([Value]from [SOURCE] where [Group] like "%G1%"..."%G6%" insert into [TARGET].[sumG1]....[sumG6] group by [SOURCE].[Site_id]

I can't get the Insert and Group syntax, and will it need one or two queries?

Thanks


Aussie Nature Shots

tjhb

7,503 post(s)
#12-Nov-17 03:34

Colin,

Does the [TARGET] table have just one row? (It sounds like it.)

Why do you want the result, and what are you going to do with it?

I'm pretty sure you'll be able to achieve your objective a better way, but if you really want SQL for exactly this then I think it can be done.

As always, sample data would answer many questions.

ColinD


1,818 post(s)
#12-Nov-17 04:33

Thanks Tim, if by one row you mean column headings then yes, an empty table.

The TARGET table will be exported as csv for input into another analysis program.

I was attempting to improve my SQL 'skills' but if there is a better way then good.

Sample tables are attached.

Oh and there is now an additional field in [SOURCE] named [Class] and in [TARGET] named [sumC2]. There are three classes but only C2 is of interest and the required result is the sum of [Value] for Class C2 grouped by [Site_id].

Attachments:
Source&Target.map


Aussie Nature Shots

tjhb

7,503 post(s)
#12-Nov-17 23:23

Colin,

Just getting back to this.

The .map file can't be downloaded--I suspect that the ampersand in the file name is not handled correctly by the forum.

Could you please reattach it with a different name?

ColinD


1,818 post(s)
#13-Nov-17 00:06

Ta, try again. Yes! that works.

Attachments:
SourceTarget.map


Aussie Nature Shots

tjhb

7,503 post(s)
#13-Nov-17 00:30

Good, thanks Colin.

Will the [Group] names be exactly "G1", "G2", ..., or will they only contain those strings? I am wondering about your use of

where [Group] like "%G1%"..."%G6%"

above.

And can you put what you need into English? E.g. "For each Site_id, the number of different(?) groups present, and for each group, the total value". Is that it?

ColinD


1,818 post(s)
#13-Nov-17 01:36

I have only provided dummy names, hoping to replace those with real after it is working. If you would prefer the real then we would need to go offline. I need for each site the count of each group and the sum of the values for each group and the sum of the values for Class C2 for each group. Thanks


Aussie Nature Shots

tjhb

7,503 post(s)
#13-Nov-17 01:57

I have only provided dummy names, hoping to replace those with real after it is working.

That's fine but it makes a difference to syntax whether the real names will be whole names or part names. (Capitalization also makes a difference but not much to syntax.)

I need for each site the count of each group and the sum of the values for each group and the sum of the values for Class C2 for each group.

The "correct" way to present that is in a target table like this.

site_id | group_name | n_values | sum_values_all | sum_values_C2

(Column names don't matter, just for illustration.)

Could you live with that?

Rather than going across the page with specially named columns (though that can be done), it would go down the page with one row per (site_id, group_name) pair.

ColinD


1,818 post(s)
#13-Nov-17 02:05

Unfortunately not because the format In Target is required by the program the csv gets entered in. Given those issues you describe I had best email you the real thing. But later today as I’m in the bush now.


Aussie Nature Shots

tjhb

7,503 post(s)
#13-Nov-17 02:22

That would be good. It's not too hard to pivot into the format needed for output.

steveFitz

158 post(s)
#13-Nov-17 10:36

Here is one possible solution using multiple queries.

Not very elegant though!

Run 'QueryAll' query

Steve

Attachments:
SourceTarget_PivotSolve.map

ColinD


1,818 post(s)
#13-Nov-17 10:42

Thanks Steve I'll give that a run.


Aussie Nature Shots

tjhb

7,503 post(s)
#13-Nov-17 21:38

Seems perfect to me. (Could tidy up aliases, remove a few brackets, and might need to adjust output column names. Always room for improvements like this, therefore perfect.)

ColinD


1,818 post(s)
#13-Nov-17 22:41

On the realistic condition that there are no C2 classes the TARGET table is empty. On the condition that only some Site_id contain Class C2 the TARGET table only has values for those Site_id that contain Class C2. Absence of Class C2 should result in zero in Class C2 for each Site_id and of course for the other fields filled.


Aussie Nature Shots

tjhb

7,503 post(s)
#13-Nov-17 23:32

Good point. Easily fixed.

Attachments:
SourceTarget_PivotSolve_adjusted.map

steveFitz

158 post(s)
#14-Nov-17 06:25

Left join makes much more sense. Coalesce - that's something I haven't seen before!

Nice work tjhb

ColinD


1,818 post(s)
#14-Nov-17 08:35

Thanks so much to both of you


Aussie Nature Shots

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.