sqloracle-databasealiasms-querymicrosoft-query

SUM of DISTINCT in Microsoft Query


I have a query that returns data somewhat like this:

REF01   10  50  1
REF01   10  50  1
REF01   20  40  1
REF01   20  40  1
REF01   30  30  2
REF02   40  20  1
REF02   50  10  2
REF02   50  10  2

And I need it to return it like this:

REF01   60  120
REF02   90  30

To do so first I add a DISTINCT (GROUP BY with all the columns also seems to do the same) to remove the duplicates which returns it like this:

REF01   10  50  1
REF01   20  40  1
REF01   30  30  2
REF02   40  20  1
REF02   50  10  2

And then I need to add a SUM() of that result without the removed duplicated ones being counted.

I have tried solutions like this one but I get an error with the FROM (SELECT ...) and this one which does work except that one of the columns inside the subquery is the result of an operation (column_1-column_2-column_3) which causes the error ORA-00972: identifier is too long for exceeding the 30 characters maximum limit and trying to apply an alias with AS in the subquery (inside the WITH SUBQUERY AS ( ... ) isn't working in MS Query from what I have tried.

Support for most SQL functions and such isn't the best on Microsoft Query.


Solution

  • Edit - This is the solution that worked:

    The issue seems to be related to the auto-generated column names, that end up being too long for oracle. To avoid this problem, the query can explicitly name the CTE columns, as shown below:

    with
    x (col1, col2, col3, col4) as ( -- columns are named here
      select distinct col1, col2, col3, col4 from t
    )
    select col1, sum(col2), sum(col3)
    from x
    group by col1
    

    Solution #2 (that didn't work in the tool):

    If the tool you are using does not have support for subqueries, you can still trick it by creating a view instead. For example:

    create view view1 as select distinct col1, col2, col3, col4 from t
    

    Then, just run a query that uses it:

    select col1, sum(col2), sum(col3) from view1 group by col1
    

    Result:

    COL1   SUM(COL2)  SUM(COL3)
    -----  ---------  ---------
    REF02         90         30
    REF01         60        120
    

    Solution #3 (that didn't work in the tool either):

    select col1, sum(col2), sum(col3) from (
      select distinct col1, col2, col3, col4 from t
    ) x
    group by col1
    

    Data used for testing:

    For reference, the data script I used is:

    create table t (
      col1 varchar2(10),
      col2 number(6),
      col3 number(6),
      col4 number(6)
    );
    
    insert into t (col1, col2, col3, col4) values ('REF01', 10, 50, 1);
    insert into t (col1, col2, col3, col4) values ('REF01', 10, 50, 1);
    insert into t (col1, col2, col3, col4) values ('REF01', 20, 40, 1);
    insert into t (col1, col2, col3, col4) values ('REF01', 20, 40, 1);
    insert into t (col1, col2, col3, col4) values ('REF01', 30, 30, 1);
    insert into t (col1, col2, col3, col4) values ('REF02', 40, 20, 1);
    insert into t (col1, col2, col3, col4) values ('REF02', 50, 10, 1);
    insert into t (col1, col2, col3, col4) values ('REF02', 50, 10, 1);