Dashboard Discussion, Support, Examples, Help » How-To

Grouping data in a Qlet

(4 posts)

  1. toma
    Member

    Hi, I am trying to get a grand total of a sum of records:

    select column1, sum(column2)
    from table
    group by column1, column2
    with rollup

    When I run this I get the following error
    Syntax error in WITH OWNERACCESS OPTION declaration

    Any ideas?

    Posted 1 year ago #

  2. admin
    Key Master

    Hi Toma,
    I think this is a database specific error. InfoCaptor passes the query as it is to the database.

    I found some reference to this for a possible cause/solution
    http://office.microsoft.com/en-us/access/HP010322891033.aspx

    Is this an Access database?

    Posted 1 year ago #

  3. toma
    Member

    Hi thanks for the quick response. This is actually an excel worksheet.

    Initially I was having trouble showing the grand total of a group of records counted as follows:

    select product, count(*) as "Total"
    from table
    group by product

    So then I decided to create an additional column in the worksheet with basically is a sum of each record. From there I came up with the SQL in my first post and received the error. I have also tried to add the WITH OWNERACCESS OPTION into the SQL however this still didn't work.

    I would prefer not to have to add the additional column but get the grand total of a count of records.

    If you have any ideas it would be mcuh appreciated.

    Posted 1 year ago #

  4. njethwa
    Member

    Hi Toma,
    You could try a union query as below

    select product , count(*) as "Total"
    from table
    group by product
    union
    select 'Grand Total' , count(*) as "Total"
    from table
    group by product

    -----------------------
    The only problem with the above query would be that the Grand Total may actually end up anywhere as it tries to sort on the product column

    so we can refine the query as below by adding a sequence and wrap the entire query as a table

    select product, Total
    from
    (
    select 1 as sequence, product , count(*) as Total
    from table
    group by product
    union
    select 2 as sequence, 'Grand Total' , count(*) as Total
    from table
    group by product
    ) as union_table
    order by sequence

    Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.