[teiid-issues] [JBoss JIRA] (TEIID-2443) Support Language feature for "With Rollup"

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2443?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12793156#comment-12793156 ] 

Steven Hawkins commented on TEIID-2443:
---------------------------------------

It is straight-forward for us to add support for grouping by a single rollup element:

group by rollup(col1, col2 ...)

which is the standard syntax ("WITH ROLLUP" is an older style syntax).  The spec also allows for cube, grouping sets, and the ability to intermix the grouping elements - group by col1, rollup(col2 ...)

with the exception of allowing just a single cube grouping element that additional work requires a great deal more logic.  It still seems worth while though to specifically add rollup support with pushdown to mysql, sql server, etc.
                
> Support Language feature for "With Rollup"
> ------------------------------------------
>
>                 Key: TEIID-2443
>                 URL: https://issues.jboss.org/browse/TEIID-2443
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>            Reporter: Ramesh Reddy
>            Assignee: Steven Hawkins
>              Labels: analytical, olap, teiid
>             Fix For: 8.5
>
>
> It would be useful to provide the clause 'WITH ROLLUP' in Teiid for analytical type queries. Example:
> {code}
> Group                Name                 SalesYTD
> North America        Northwest            123237.00
> North America        Northwest             37534.00
> North America        Northwest             48003.00
> North America        Southwest            164232.00
> North America        Southeast             39667.00
> North America        Southeast            105810.00
> Europe               France                74569.00
> Europe               Germany               59456.00
> Pacific              Australia             93403.00
> Europe               United Kingdom        78327.00
> {code}
> With query like 
> {code:SQL}
> SELECT      [Group], [Name], SUM([SalesYTD]) AS 'Total Sales'
> FROM        Table
> GROUP BY    [Group], [Name] WITH ROLLUP 
> {code}
> will produce
> {code}
> Group                Name                 Total Sales
> Europe               France                74569.00
> Europe               Germany               59456.00
> Europe               United Kingdom        78327.00
> Europe               NULL                 212352.00
> North America        Northwest            208774.00
> North America        Southeast            145477.00
> North America        Southwest            164232.00
> North America        NULL                 518483.00
> Pacific              Australia             93403.00
> Pacific              NULL                  93403.00
> NULL                 NULL                 824238.00
> {code}
> 4 rows have been added, 3 for sub total of each [Group], and 1 row for grand total.
> Teiid needa to build it's analytical function library to be useful in reporting situations.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira