CODEWITHSUNDEEP
sqloracle-database
kang
kang

Reputation: 707

Sum and joined column data in same query

I am using Oracle DB and have the following query which throws the error:

ORA-00937: not a single-group group function

Query:

select sum(ct.TOT_CT), ct.CREAT_TS, ev.EVENT
    from REF_CT ct
    inner join EVENT ev on ev.EVENT_TYPE_ID = ct.EVENT_TYPE_ID
    where ct.TX = 'SomeText';

If I try to access the following individually, it works. As in calling sum separately, ct.CREAT_TS separately and so on.

The issue seems to be calling sum in this query. I plan to capture the output in an Object (Java if relevant). I expected the query to output following. Is it possible to achieve this?

CREAT_TS                      EVENT          sum(ct.TOT_CT)
2019-04-02 01:33:46.227000    SOME_EVENT 1   8
2019-04-02 02:56:14.530000    SOME_EVENT 2

After Given answers, getting following results where the sum value is split per column:

CREAT_TS                      EVENT          sum(ct.TOT_CT)
2019-04-02 01:33:46.227000    SOME_EVENT 1   2
2019-04-02 02:56:14.530000    SOME_EVENT 2   6

Upvotes: 2

Views: 56

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use window functions to put the total sum on each row:

select ev.EVENT, ct.CREAT_TS, sum(ct.TOT_CT), 
       sum(sum(ct.TOT_CT)) over ()
from REF_CT ct inner join
     EVENT ev
     on ev.EVENT_TYPE_ID = ct.EVENT_TYPE_ID
where ct.TX = 'SomeText'
group by ev.EVENT, ct.CREAT_TS;

I don't really recommend putting the value on only one row, but you can do that with window functions:

select ev.EVENT, ct.CREAT_TS, sum(ct.TOT_CT), 
       (case when 1 = row_number() over (partition by ev.event order by ct.CREAT_TS)
             then sum(sum(ct.TOT_CT)) over ()
        end)
from REF_CT ct inner join
     EVENT ev
     on ev.EVENT_TYPE_ID = ct.EVENT_TYPE_ID
where ct.TX = 'SomeText'
group by ev.EVENT, ct.CREAT_TS
order by ev.EVENT, ct.CREAT_TS;

The order by is very important if you want the grand total on the first row defined by CREAT_TS.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

If at least one aggregated column exists in the select list, you need to group by non-aggregated columns 

select sum(ct.TOT_CT), ct.CREAT_TS, ev.EVENT
    from REF_CT ct
    inner join EVENT ev on ev.EVENT_TYPE_ID = ct.EVENT_TYPE_ID
    where ct.TX = 'SomeText'
    group by ct.CREAT_TS, ev.EVENT

Upvotes: 1

M. Kanarkowski
M. Kanarkowski

Reputation: 2205

You need to include group by clause.

select sum(ct.TOT_CT), ct.CREAT_TS, ev.EVENT
    from REF_CT ct
    inner join EVENT ev on ev.EVENT_TYPE_ID = ct.EVENT_TYPE_ID
    where ct.TX = 'SomeText'
    group by ct.CREAT_TS, ev.EVENT;

Upvotes: 1

Related Questions