0

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.

user7393973
  • 2,270
  • 1
  • 20
  • 58

2 Answers2

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);
The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • When I try to do that I get an error saying that it wasn't possible to add the the table '('. – user7393973 Jun 03 '19 at 14:28
  • It must be the tool you are using to create queries. – The Impaler Jun 03 '19 at 14:30
  • In order to produce the result you want, you necessarily need a subquery. If the tool you are using doesn't allow them, you are out of luck. – The Impaler Jun 03 '19 at 14:31
  • I'm using Microsoft Query since I need to use it with Microsoft Excel. It does allow subqueries at least using the `WITH subquery_name AS ( subquery )` method but with that way I'm running into the problem described in the question. – user7393973 Jun 03 '19 at 14:34
  • I wrote another solution... using views. Maybe you can try this one. – The Impaler Jun 03 '19 at 14:34
  • Trying to create a view threw the error saying that I don't have enough privileges, possibly since the database that I'm getting the data from is read-only. – user7393973 Jun 03 '19 at 14:42
  • 1
    Coming back to your original solution, Oracle is complaining the [automatically] generated column names of the CTE are too long. But those ones are configurable. For example: `WITH x (col1, col2, col3, col4) as ()`. Maybe your tool does have an option to specify them. Just a tought. – The Impaler Jun 03 '19 at 14:45
  • Using `WITH x (column aliases) AS ()` did solved the issue! Thank you :) – user7393973 Jun 03 '19 at 14:50
  • I'll accept your answer since your solution in the comment worked. If you can, please edit and add that solution to the answer. – user7393973 Jun 07 '19 at 11:26
  • @user7393973 Solution added. – The Impaler Jun 07 '19 at 15:12
1

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
iainc
  • 862
  • 6
  • 20