0

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 can this be achieved in SAP HANA graphical calculation view?

Boghyon Hoffmann
  • 17,103
  • 12
  • 72
  • 170
Anirudh D
  • 181
  • 3
  • 14
  • 33

2 Answers2

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. Using Calculation Views could be an entry point for reading the documentation.

Boghyon Hoffmann
  • 17,103
  • 12
  • 72
  • 170
Lars Br.
  • 9,949
  • 2
  • 15
  • 29
0

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,

Eralper
  • 6,461
  • 2
  • 21
  • 27