0

I have a select

select distinct
    min(trunc(ALL_SUBS_DAY.CHARGE_AMT * SUP.SHARE_USAGE, 4)) + (ALL_SUBS_DAY.CHARGE_AMT - sum(trunc(ALL_SUBS_DAY.CHARGE_AMT * SUP.SHARE_USAGE, 4))) as CHARGE_AMT
from table names;

when I try to run it I get not a single-group group function. If I do it like this

select 
    min(trunc(ALL_SUBS_DAY.CHARGE_AMT * SUP.SHARE_USAGE, 4)) + (ALL_SUBS_DAY.CHARGE_AMT - sum(trunc(ALL_SUBS_DAY.CHARGE_AMT * SUP.SHARE_USAGE, 4))) as CHARGE_AMT
from table names
group by SUBS_DAY.CHARGE_AMT, SUP.SHARE_USAGE;

it works. Wasn't distinct supposed to work like group by? Do I really need to list every single column or is there a way not to do it?

gjin
  • 860
  • 1
  • 14
  • 28
  • Have a look at [this question](http://stackoverflow.com/questions/164319/is-there-any-difference-between-group-by-and-distinct). – Aleksej Dec 13 '16 at 15:12

1 Answers1

1

The issue is with ALL_SUBS_DAY.CHARGE_AMT.
You are not grouping by it, nor using an aggregate function on it.

+ (ALL_SUBS_DAY.CHARGE_AMT - sum(

select distinct min(trunc(ALL_SUBS_DAY.CHARGE_AMT * SUP.SHARE_USAGE, 4)) + (ALL_SUBS_DAY.CHARGE_AMT - sum(trunc(ALL_SUBS_DAY.CHARGE_AMT * SUP.SHARE_USAGE, 4))) as CHARGE_AMT from table names;

DISTINCT work like group by only if you group by all columns/expressions and select all columns/expressions, e.g. -

select distinct a,b,c,d,e from t;

=

select a,b,c,d,e from t group by a,b,c,d,e;

Your first query returns a single row because you didn't aggregate by anything, which makes the distinct meaningless.

David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88