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?
Aussie Nature Shots
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.
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].
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?
Ta, try again. Yes! that works.
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%"
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?
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
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.
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.
That would be good. It's not too hard to pivot into the format needed for output.
Here is one possible solution using multiple queries.
Not very elegant though!
Run 'QueryAll' query
Thanks Steve I'll give that a run.
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.)
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.
Good point. Easily fixed.
Left join makes much more sense. Coalesce - that's something I haven't seen before!
Nice work tjhb
Thanks so much to both of you