Query Total Per Field/Where Clause

[bbranco]

Hi,
I have a 2 part questions:

I have a query in Hana that generates

Bin Number    Item Number    Onhand

000-A-201      ABC                  100
000-B-201      CDE                  300
000-A-201      DDD                  400
000-B-201      XXX                   250
000-B-201      YYY                   300
000-B-201      XXX                   250

000-P-100      XXX                   125

1) I need to exclude any bins where it contains -P- 
2) I need the output to be
    Bin Number   Items
    000-A-201     2
    000-B-201     4

    I need to know how many specific items are in each bin, the quantity does not matter

Any help would be appreciated

[BattleshipCobra · Accepted solution]

You just need to use an aggregate with COUNT() and then a WHERE with NOT LIKE and a wildcard.

SO, I don't see your query but something like:

SELECT "BinNumber" AS "Bin Number",COUNT("ItemCode") AS "Items"

FROM X (whatever your specific query is)

WHERE "BinNumber" NOT LIKE '%-P-%'

GROUP BY "BinNumber"

The % is wildcard so it looks for any BIN not containing the '-P-' string.  Then to use the COUNT aggregate you need to group by the "BinNumber".  If your dataset contains multiple of the same ItemCode you can use a COUNT( DISTINCT "ItemCode") as documented here:

https://help.sap.com/docs/HANA_SERVICE_CF/7c78579ce9b14a669c1f3295b0d8ca16/28c3b573c4354f75a1931202e...

Is this enough to get you where you need to go?  Give it a try and post what you used.  Good luck!

Mike

[bbranco · Accepted solution]

Thanks again, I am good