Skip to Content
avatar image
Former Member

HANA Query group by but add values

Hello everyone,

I am trying to write a query in HANA that achieves the following:

So I want to make the Sales_ID unique. At the same time I want to add the Revenue of each Sales_ID.

What I did so far: Selecting Sales_ID and Revenue, group by Sales_ID and sum up the revenue. So far so good. But it doesnt work when I add the field "Product_ID" since there are different values in each row. (so it doesnt group anymore) But I'm good if I just have the Product_ID of the first row of each Sale.

How do I do that?

Greetings

Capture.PNG (37.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 04, 2015 at 11:37 AM

    You could try min(Product_ID).

    Besides that, I'd suggest not to discuss the meaning of the term 'first row' in a relational database ...

    regards

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 04, 2015 at 11:43 AM

    First get result using group by and sum without product id and then join it with the same table:

    Query 1=select "Sales_Id","Product_Id" from table A where "revenue" = (select Min/Max("revenue") from table B where  B."Sales_Id"=A."Sales_ID"

    Query 2=select "Sales_id",Sum("revenue") from table group by "Product_id").

    Now Do an Inner join of result of both queries .

    Regards,

    Pranjal

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 09, 2015 at 11:02 AM

    you can write in single query

    Select "Sales_id","Client_name",Min("Product_id"),sum("revenue")

      from table

      group by "sales_id", "client_name"

    Regards,

    AK

    Add comment
    10|10000 characters needed characters exceeded