COUNT(*) giving wrong results

[Former Member]

Hi all,

     I am facing a very peculiar issue here. Will take it up with the help of queries -

SELECT COUNT(*) FROM table_name WHERE "column_name" = 'elec'; 

-- this is giving me 2 rows.

SELECT COUNT(*)

  FROM (

       SELECT * FROM table_name WHERE "column_name" = 'elec'

  );   

---this gives 731 rows which is the CORRECT value.

SELECT COUNT(*) FROM (

            SELECT * FROM table_name

              )

WHERE "column_name" = 'elec';

---this also gives 731 rows which is the CORRECT value.

I have no clue what is I am doing wrong here. It is happening for queries with a 'WHERE' clause. I looked at the PlanViz fir these two queries, but could not make anything out of it. Any help would be appreciated

[lbreddemann]

To understand this a bit better:

Are you actually querying a table or something that looks like a table, e.g. a view, a calc view or a virtual table?

What is the SAP HANA revision you're using?

Can you provide the explain plan for both the correct and the incorrect result examples?

[Astrid_Gambill]

I've got the same problem.  Studio 2.1.19 against SPS 9 REV 96, against a calculated view

basic count statement returns value of 1, when it should be 2.

selecting all columns from the same table for the same account returns the expected 2 rows.

wrapping a count around the basic select, does bring back the expected count of 2.

It looks like there's an implicit DISTINCT being used when a specific column is being selected.

This statement brings back the expected 10 rows.

select count(*), vkont

from

(select *

from "_SYS_BIC"."WIP.AVAYA_PM/ZQCV_M2C_CONTRACT_ACCOUNT" accts

)

group by vkont

having count(*) > 1

whereas, this brings back nothing

select count(*), vkont

from

(select accts.vkont

from "_SYS_BIC"."WIP.AVAYA_PM/ZQCV_M2C_CONTRACT_ACCOUNT" accts

)

group by vkont

having count(*) > 1

Message was edited by: Astrid Gambill

[lbreddemann]

Ok, first of all: please don't hijack threads.

I get it, you have a problem that looks similar and everything, but 'me too' posts don't really add value and are against

Now to the question itself. I'd say: that's all in the documentation. It has been discussed here on SCN. It's in SAP notes. It's definitively possible to find what's going on there.

Maybe and the list of SAP notes mentioned in this discussion shed some more light onto the topic:

A5: Calc Engine is pretty special and I'd recommend to give the sap notes including their attachments  a read:

SAP note HANA Calculation Engine Instantiation Process https://service.sap.com/sap/support/notes/1764658
SAP notePerformance degradation for query on CalcView https://service.sap.com/sap/support/notes/1825549
SAP note Select count(*) on calculated views
https://service.sap.com/sap/support/notes/1791464
SAP note Duplicate dimension values for query on Calculation View
https://service.sap.com/sap/support/notes/1783880
SAP note Select on a calculated attribute returns empty result
https://service.sap.com/sap/support/notes/1764662

[Former Member]

Just an update -

I got the auto generated query from the log (placed the column in the label as well as the value axis to get the column name + count). The query generated is the same as the second and the third in my question. Any specific reasons for this?

SELECT "column_name", COUNT(*) AS "column_name_COUNT"

FROM (SELECT  * FROM table_name")  --had all the columns, so removed them for readibility

where "column_name" = 'elec' -- --added post the generated query to match the result

GROUP BY "column_name"

ORDER BY "column_name" ASC

-- Result - 731 records

[henrique_pinto]

This is different, because you have a group by column which you didn't have before.

But I'm not sure why it's generating it like this for you, I use select col1, count(col2) from table all the time and it works.

[henrique_pinto]

Do you have a non-null column in this table?
Try SELECT COUNT(<column>) instead of COUNT(*).

[Former Member]

hey Henrique, i have Null values in my columns. So COUNT(<column>) will not work in my scenario.

[nithinu]

Hello Sanjog,

Does the following query give you two rows ? Or one row with count as 2 ?

SELECT COUNT(*) FROM table_name WHERE "column_name" = 'elec';

-- this is giving me 2 rows.

Regards,

Nithin

[Former Member]

It is giving me also 2 rows.