Author Topic: Showing the COUNT-Ratio of seperate Select statements in a PieChart  (Read 1533 times)

wzr

  • EA Novice
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Hello,

lets say I'm having two (or more) seperate SELECT statements. Each of them return a number of Elements from t_object.
What I would like to show in a PieChart is the ratio of the COUNT result of the SELECT statements to each other.

I.e. a simple query like this:
SELECT t_object.Name FROM t_object WHERE t_object.Name LIKE 'Example_#WC#'
giving 10 rows as result

and another one:
SELECT t_object.Name FROM t_object WHERE t_object.Name LIKE 'Example2_#WC#'
giving 5 rows as result

I would like to create a PieChart showing a 10/5 ratio.

Sounds easy but I can't get it done   :-\

Thanks a lot!

wzr

  • EA Novice
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Re: Showing the COUNT-Ratio of seperate Select statements in a PieChart
« Reply #1 on: September 28, 2021, 09:19:12 pm »
No suggestions?
Is the question unclear? Or is there no easy solution?

Thank you!

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 11251
  • Karma: +415/-33
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: Showing the COUNT-Ratio of seperate Select statements in a PieChart
« Reply #2 on: September 28, 2021, 10:19:13 pm »
You can always combine separate select queries as subqueries or with unions into a larger query.

e.g.

Code: [Select]
SELECT o.Name, 'Example' as Category
FROM t_object o WHERE
o.Name LIKE 'Example_#WC#'
union
SELECT o.Name, 'Example2' as Category
FROM t_object o WHERE
o.Name LIKE 'Example2_#WC#'

Does that help?

Geert

Eve

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 7571
  • Karma: +94/-18
    • View Profile
Re: Showing the COUNT-Ratio of seperate Select statements in a PieChart
« Reply #3 on: September 29, 2021, 10:12:11 am »
Assuming you're talking about the EA chart objects...

https://sparxsystems.com/enterprise_architect_user_guide/16.0/model_publishing/chart_query.html

You need to return a column 'Series', which gives you

Code: [Select]
SELECT 'Example' as Series
FROM t_object o WHERE
o.Name LIKE 'Example_#WC#'
union
SELECT 'Example2' as Series
FROM t_object o WHERE
o.Name LIKE 'Example2_#WC#'
Eve

support@sparxsystems.com

wzr

  • EA Novice
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Re: Showing the COUNT-Ratio of seperate Select statements in a PieChart
« Reply #4 on: September 30, 2021, 04:25:16 am »
Thanks for your help.
I got what I was looking for with an attempt like this:

Code: [Select]
SELECT o.Name, 'Example' as Series
FROM t_object o WHERE
o.Name LIKE 'Example_#WC#'
union
SELECT o.Name, 'Example2' as Series
FROM t_object o WHERE
o.Name LIKE 'Example2_#WC#'

If the 'Series' column is the only one selected, its always a 50/50 ratio, cause the result is only one row for each Select query.
The 'Category' column Geert proposed is propably for another Chart Element? My StandartChart (EA 15.1) doesnt process that one.

Eve

  • EA Administrator
  • EA Guru
  • *****
  • Posts: 7571
  • Karma: +94/-18
    • View Profile
Re: Showing the COUNT-Ratio of seperate Select statements in a PieChart
« Reply #5 on: September 30, 2021, 08:17:31 am »
Sorry, that's the other change I was going to make. You need 'union all'.
Eve

support@sparxsystems.com