0008701: Returning SQL results for running sums/totals - Valentina Database Mantis

ID	Project	Category	View Status	Date Submitted	Last Update

0008701	VALENTINA SERVER	Feature	public	2019-12-14 17:24	2020-02-15 09:59

Reporter	Dale Evankovich	Assigned To	Ivan Smahin
Priority	high	Severity	major	Reproducibility	N/A
Status	resolved	Resolution	fixed
Platform	ALL	OS	ALL	OS Version	ALL
Product Version	9.6.x
Target Version	10.0.x	Fixed in Version	10.0.x

Summary	0008701: Returning SQL results for running sums/totals
Description	Other database systems provide the OVER and PARTITION BY functions to effectively return running sums/totals in executed SQL queries. Any update as to when Valentina Server can provide this capability. I am using a temporary work around using an old school SQL approach, (subquery in the select clause), but things are getting progressively slower and slower as the table gets larger and larger. I could really use this capability which is readily available on other database platforms.
Tags	No tags attached.

Ivan Smahin 2019-12-16 06:53 manager ~0011108	Currently we support "SELECT f1, SUM(f2) GROUP BY f1 WITH ROLLUP" only. Probably it would help.

Ivan Smahin 2019-12-16 06:58 manager ~0011109	SalesWithRollup -------------------------------------- year country product profit -------------------------------------- 2000 Finland Computer 1500 2000 Finland Phone 100 2000 India Calculator 150 2000 India Computer 1200 2000 USA Calculator 75 2000 USA Computer 1500 2001 Finland Phone 10 2001 USA Calculator 50 2001 USA Computer 2700 2001 USA TV 250 SELECT year, country, product, SUM(profit) FROM SalesWithRollup GROUP BY year, country, product WITH ROLLUP; +------+---------+------------+-------------+ \| year \| country \| product \| SUM(profit) \| +------+---------+------------+-------------+ \| 2000 \| Finland \| Computer \| 1500 \| \| 2000 \| Finland \| Phone \| 100 \| \| 2000 \| Finland \| NULL \| 1600 \| \| 2000 \| India \| Calculator \| 150 \| \| 2000 \| India \| Computer \| 1200 \| \| 2000 \| India \| NULL \| 1350 \| \| 2000 \| USA \| Calculator \| 75 \| \| 2000 \| USA \| Computer \| 1500 \| \| 2000 \| USA \| NULL \| 1575 \| \| 2000 \| NULL \| NULL \| 4525 \| \| 2001 \| Finland \| Phone \| 10 \| \| 2001 \| Finland \| NULL \| 10 \| \| 2001 \| USA \| Calculator \| 50 \| \| 2001 \| USA \| Computer \| 2700 \| \| 2001 \| USA \| TV \| 250 \| \| 2001 \| USA \| NULL \| 3000 \| \| 2001 \| NULL \| NULL \| 3010 \| \| NULL \| NULL \| NULL \| 7535 \| +------+---------+------------+-------------+

Ivan Smahin 2020-02-15 09:51 manager ~0011141	Some window functions are implemented in v.10.0. Please see https://valentina-db.com/docs/dokuwiki/v10/doku.php?id=valentina:vcomponents:vsql:reference:expr:funcs_window

Date Modified	Username	Field	Change
2019-12-14 17:24	Dale Evankovich	New Issue
2019-12-16 06:53	Ivan Smahin	Note Added: 0011108
2019-12-16 06:58	Ivan Smahin	Note Added: 0011109
2020-02-15 09:50	Ivan Smahin	Target Version	=> 10.0.x
2020-02-15 09:51	Ivan Smahin	Note Added: 0011141
2020-02-15 09:59	Ivan Smahin	Assigned To	=> Ivan Smahin
2020-02-15 09:59	Ivan Smahin	Status	new => resolved
2020-02-15 09:59	Ivan Smahin	Resolution	open => fixed
2020-02-15 09:59	Ivan Smahin	Fixed in Version	=> 10.0.x