Pie chart which includes multiplicity

[Hurra]

Hi!

I have a pie chart which shows number of roles (UAF::ActualPost) in an organization.

Code: [Select]

SELECT system.Name AS Series, systemElement.Name AS GroupName
FROM t_object systemElement
INNER JOIN t_object system ON system.Object_ID = systemElement.ParentID
INNER JOIN t_object organization ON organization.ParentID = systemElement.Object_ID
INNER JOIN t_object post ON post.ParentID = organization.Object_ID AND post.Stereotype = 'ActualPost'

WHERE system.Name = 'Name of my main system object'

Which give me:


However, each ActualPost have multiplicity. With this SQL I get the value of one, since the query doesn't account for multiplicity. I know I can get multiplicity with t_object.Multiplicity.

Like this:

Code: [Select]

SELECT system.Name AS Series, systemElement.Name AS GroupName, post.Name AS 'Roll', post.Multiplicity AS 'Antal'
FROM t_object systemElement
INNER JOIN t_object system ON system.Object_ID = systemElement.ParentID
INNER JOIN t_object organization ON organization.ParentID = systemElement.Object_ID
INNER JOIN t_object post ON post.ParentID = organization.Object_ID AND post.Stereotype = 'ActualPost'

WHERE system.Name = 'Name of my main system object'

Which gives a table like:

Code: [Select]

Series | GroupName | Roll | Antal
Name of my main system element | OperationalPerformer A | Post AA | 1
Name of my main system element | OperationalPerformer A | Post AB | 1
Name of my main system element | OperationalPerformer A | Post AC | 2
Name of my main system element | OperationalPerformer B | Post BA | 1
Name of my main system element | OperationalPerformer B | Post BB | 1
Name of my main system element | OperationalPerformer C | Post CA | 3
Name of my main system element | OperationalPerformer C | Post CB | 2
Name of my main system element | OperationalPerformer C | Post CC | 5

I'm not sure how I would add the multiplicity to the 'GroupName' column. Should I try to just add the multiplicity as new rows? Something like this:

Code: [Select]

Series | GroupName | Roll | Antal
Name of my main system element | OperationalPerformer A | Post AA | 1
Name of my main system element | OperationalPerformer A | Post AB | 1
Name of my main system element | OperationalPerformer A | Post AC | 2

into

Code: [Select]

Series | GroupName
Name of my main system element | OperationalPerformer A
Name of my main system element | OperationalPerformer A
Name of my main system element | OperationalPerformer A
Name of my main system element | OperationalPerformer A

I have no idea how would achieve this though..

Is there a better approach?

Thank you for any input!

[Hurra]

Got an answer on stack overflow:

https://stackoverflow.com/questions/67021466/add-multiplicity-as-rows