SQL Query in Calc

[Redart15]

The following problem has been bothering me for a few days and as such i decided to ask for help.

I have a list of data in help.ods. The data is stored in calc, as I intend add visualize the data later on.
Idealy I want the data be stored in cal.

I want to realize the following query:

select name, sum(amount)as AmountBought,
AVG(price) as avgPrice, min(price) as BestPrice, max(price) as worstPrice
from liste
group by name
having AmountBought > 1
order by AmountBought desc
limit 1;

Result of said Query:

Here are all the thing I have tried:
• using build in functions → to complex and not even sure if possible
• pivit tables on calc → couldn’t find a way to sort by result column, to many tables
• connect base to calc → couldn’t find a dynamic way to do it(only copies), cumbersome to update data
• pivit table of base → works, cumbersome to update data

Thing I have not tried yet (a link to a tutorial would be nice):
• Macros
• programming languages such as python

Attachments:
help.ods (754.8 KB)

[karolus]

thats looks easy…see Pivot in attached Document
sql_alas_pivot.ods (771.6 KB)

edit: sorry forget to sort descending … click »amount« …choose »descending«

[Redart15]

Thank, however it not quite what I was looking for. I have updated the query and added a picture of what I am trying to archive with libre calc.

[Villeroy]

Spreadsheets and databases are like air and water. Strictly separated but interacting in some ways. This office suite comes with a database component.

[Tutorial] Using registered datasources in Calc

[Redart15]

Thanks, now the database is properly connected to the spreadsheet.
However I have still no idea how to get the query results from the LibreOffice Base to be imported to calc itself.

[karolus]

with sum of amount
if amount > 1 ( Filter)
sql_alas_pivot_include_sum_amounts.ods (754.9 KB)