How to populate a pie chart (i.e any chart) with table data?

[alexandlazaris]

Hi there,

I have a query which fetches item and unique counts of each item in a table. I want to now use this output to render a pie chart.

https://sql.ophir.dev/documentation.sql?component=chart#component

I don't see an example of it so far in the docs. I have been able to populate options for a form field though so I believe there is hope. Is this supported?

The query used is something like:

SELECT item, count(*)
FROM Items
GROUP BY item
ORDER by count(*) DESC;

[lovasoa]

Hello and welcome to SQLPage !

What you want is probably

select 'chart'  as component, 'pie' as type;

SELECT item as label, count(*) as value
FROM Items
GROUP BY item;

Here is a live example that you can run in your browser: https://replit.com/@pimaj62145/SQLPage-bar-chart#index.sql

Two important concepts in SQLPage are components and properties. A component is a graphical widget that you can add to your page.

• You customize the components appearance with top-level properties, that you set in a first SQL query. Most of the time you hardcode them, but you are free to make your own queries to return them. In our example above, there is one top-level parameter: type, that is set to the string 'pie':

  select 'chart' as component, 'pie' as type;

• then you pass the data you want to display using row-level properties. In the documentation, as you noticed, the data is hardcoded in all examples. This allows us to write examples that will work on any database, whatever your database schema and your data. But in your application, most of the time, you don't want to hardcode the data. You want to make a query to your own tables, and just change the column names of the result to match the row-level properties that the component expects. In our case, we set label and value:

  SELECT item as label, count(*) as value FROM Items GROUP BY item;

[alexandlazaris]

Awesome thanks so much @lovasoa . That's so obvious now, I must have rushed over the simplicity of it.

Yeah I've had to ensure I don't mess up the row level properties. The documentation has been really helpful so far. Enjoying the tool, cheers!