Subscribe to this thread
Home - General / All posts - Create subgroups using percentages
Mike Pelletier

2,116 post(s)
#27-Mar-23 18:20

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

sga30 post(s)
#28-Mar-23 09:03

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

Mike Pelletier

2,116 post(s)
#28-Mar-23 15:05

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

Sloots

676 post(s)
#28-Mar-23 17:48

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

Mike Pelletier

2,116 post(s)
#28-Mar-23 19:04

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

volker

1,084 post(s)
#05-Apr-23 05:48

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

Dimitri


7,400 post(s)
#05-Apr-23 06:44

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.

volker

1,084 post(s)
#05-Apr-23 07:43

Thank you Dimitri


http://www.thegisservicesector.de

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