CODEWITHSUNDEEP
sqlhana
Prathamesh H
Prathamesh H

Reputation: 150

Hana SQL Logic needed

select  groupid,count(value) as x
from test_distinct_count
group by rollup (groupid);

This query gives output like :

enter image description here

What i need is to display the value 5 for all the groupids in a column say Total. Something like this :

enter image description here

how can this be achieved using Hana Sql.

Upvotes: 0

Views: 152

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

Do the window function first, then the rollup:

select groupid, count(value), max(total) as total
from (select groupid, value,
             sum(case when value is not null then 1 end) over () as total
      from test_distinct_count
      group by groupid
     ) t
group by rollup (groupid);

This is safer if you want to add more columns to the group by.

Upvotes: 0

Mani Kandan
Mani Kandan

Reputation: 107

create table #temp(groupid int,value int)

insert into #temp values(1,1) insert into #temp values(1,1) insert into #temp values(1,1) insert into #temp values(2,2) insert into #temp values(2,2)

select * from ( select groupid,count(value) as x from #temp group by rollup(groupid) )a cross apply ( select count(value) as Total from #temp )b

Upvotes: 1

Mani
Mani

Reputation: 344

Am from sql server background hope sum() over() will exist in Hana, try the below solution

create table #temp(groupid int,value int)

insert into #temp values(1,1)
insert into #temp values(1,1)
insert into #temp values(1,1)
insert into #temp values(2,2)
insert into #temp values(2,2)

select 
a.*,sum(x) over()/2 as Total
from (
select  groupid,count(value) as x
from #temp
group by rollup (groupid)
)a

Upvotes: 1

Florian Pfeffer
Florian Pfeffer

Reputation: 269

One option would be following:

with grp as ( select  groupid, count(value) as x
              from test_distinct_count
              group by rollup (groupid) ),
     cnt as ( select count(value) as total from test_distinct_count )
select grp.groupid, grp.x, cnt.total from grp cross join cnt;

Upvotes: 1

Related Questions