Struggling to find a good SQL approach to group records into new categories while summing fields but also applying a percentage to values. Normally I'd use Case to do the grouping, but I'm not seeing a way to apply percentages. The attached map file should make this clear with sample data. It would be great to find a slick SQL method because my data set is fairly complicated and the underlying is subject to change.

Any thoughts much appreciated.

Attachments:
group using proportions.map

Hi mike,

I'm not sure I understood your question correctly, so please tell me if I don't answer it. Take a look at the result table (second picture) to see if that's the result you're looking for.

I propose to create a table that lists the group and states the percentage of each category (one line per unique combination of group and category). Something like this in your dataset

Then join your newly created table with your test table. Group by [group] and sum your columns multiplied by the percentage

SELECT [group], 

Sum([A]*[percentage]) as [A],

Sum([B]*[percentage]) as [B],

Sum([C]*[percentage]) as [C],

Sum([D]*[percentage]) as [D]

FROM [test]

INNER JOIN [Table]

ON [test].[name]=[Table].[category]

GROUP BY [group]

This will result in

Again, I hope this is what you were seeking for.

See attached file.

Attachments:
group using proportions2.map

That's an excellent solution. Thank you very much!

With the use of the VALUES statement you can omit the intermediate table.

SELECT [group], 

 Sum([A]*[percentage]) as [A]

 , Sum([B]*[percentage]) as [B]

 , Sum([C]*[percentage]) as [C]

 , Sum([D]*[percentage]) as [D]

FROM

 [test]

INNER JOIN

 (

 VALUES 

 ('Group 1', 'T1', 1)

 , ('Group 1', 'T2', 0.5)

 , ('Group 1', 'T3', 0.25)

 , ('Group 2', 'T2', 0.5)

 , ('Group 2', 'T3', 0.75)

 , ('Group 2', 'T4', 1)

 , ('Group 3', 'T5', 1)  AS ([group], [category], [percentage])

 )

ON

 [test].[name] = [category]

GROUP BY 

 [group]

http://www.mppng.nl/manifold/pointlabeler

Thanks Chris! Embedding the table in the query. Slick.

Hello,

someone an idea how to group something like this:

after grouping it should looks like this:

1/1/bW -- BU50 Kie15 Es10 Fi10 SKi10 BAh5

Thanks for advance

Attachments:
Daten_BA_Forum.map

http://www.thegisservicesector.de

Use the StringJoinTokens aggregate function together with COLLECT DISTINCT.

If you use the forum search box to search on StringJoinTokens you get useful hits like this one.

There are also related topics in the user manual like this one.

Thank you Dimitri

http://www.thegisservicesector.de