SAP HANA SQL | Count different projected values in the same column

user_16465874 0
Viewed 616

I have the following query in SAP HANA. I projected a column that displays 02 results in millions of lines. I would like to count how many of this two results each DISTINCT ZCGNOTAL has.

Please help me.

SELECT ZCGINSTAL, 
       ZCGNOTAL, 
       "Latitude",
       CASE WHEN "Latitude" > '0' THEN 'ZERADA' ELSE 'COORDENADA' END AS COORD
FROM "CLB162585"."062021MOM"
hana count sql
1 Answers

As I understand the question, the OP wants to know, for every value of ZCGNOTAL how many records have a value of "ZERADA" and how many have a value of "COORDENADA" in the computed column COORD.

That can be computed by a simple multi-column GROUP BY and a COUNT aggregation "on-top" of the existing query:

WITH base_data as (
   SELECT   ZCGINSTAL
          , ZCGNOTAL 
          , "Latitude"
          , CASE 
              WHEN "Latitude" > '0' THEN 'ZERADA' 
              ELSE 'COORDENADA' 
            END AS     COORD
FROM 
     "CLB162585"."062021MOM")
SELECT
     ZCGNOTAL
   , COORD
   , COUNT(*) as COORD_CNT 
FROM 
   base_data
GROUP BY
     ZCGNOTAL
   , COORD

If desired, the query can be rewritten to not use a common-table expression (WITH CLAUSE) or a subquery, but for SAP HANA this is unlikely to yield better performance. HANA rewrites the query before optimisation anyway and resolving subqueries is part of that rewriting process.

user_41917670
Related