SUM of DISTINCT in Microsoft Query

Question

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.

Answer 1

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);

Answer 2

Seems obvious but would this not work (revised)?

 select col1, sum(col2) as col2, sum(col3)  as col3 from 
(Select col1, col2, sum(DISTINCT col3) as col3 from t
group by col1, col2) aa
group by col1

or with a WITH:

WITH tt as (Select col1, col2, sum(DISTINCT col3) as col3 from t
group by col1, col2) 

select col1, sum(col2) as col2, sum(col3)  as col3 from tt
group by col1