'Count distinct in SAP HANA graphical calculation view

we have below table as -

CUSTOMER_NO ORDER_NO ITEM_NO
C1            O1      I1
C1            O1      I2
C1            O1      I3
C1            O2      I4
C1            O2      I2

What I need is count of distinct orders and count of ITEMS per customer -

CUSTOMER_NO    ORDER_NO                ITEM_NO
C1               2(distinct)       5(not distinct)

How this can be achieved in SAP HANA graphical calculation view?

Best Regards

saphana


Solution 1:[1]

You will need to create a counter for that in the graphical calc view. This is explained at length in the modelling guide - I recommend to give that document a read.

Solution 2:[2]

Please check followins SQL Select statement,

select 
    distinct
    t1.CUSTOMER_NO, 
    (select count(distinct t2.ORDER_NO) from OrderItems t2 where t2.CUSTOMER_NO = t1.CUSTOMER_NO) as ORDER_NO,
    (select count(distinct t2.ITEM_NO) from OrderItems t2 where t2.CUSTOMER_NO = t1.CUSTOMER_NO) as ITEM_NO
from OrderItems t1

There is an easier way if we could apply the COUNT aggregate function with Partition By clause in SAP HANA as we do in SQL Server. As I see HANA SQLScript developers cannot use this syntax right now for Count() function So I used sub-selects as seen in above SELECT code

I hope it helps,

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Lars Br.
Solution 2 Eralper

Related Questions