View Issue Details

IDProjectCategoryView StatusLast Update
0008701VALENTINA SERVERFeaturepublic2020-02-15 09:59
ReporterDale EvankovichAssigned ToIvan Smahin 
PriorityhighSeveritymajorReproducibilityN/A
Status resolvedResolutionfixed 
PlatformALLOSALLOS VersionALL
Product Version9.6.x 
Target Version10.0.xFixed in Version10.0.x 
Summary0008701: Returning SQL results for running sums/totals
DescriptionOther 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.
TagsNo tags attached.

Activities

Ivan Smahin

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

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

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

Issue History

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