Skip to Content
avatar image
Former Member

COUNT(*) giving wrong results

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 😊

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Apr 15, 2016 at 12:23 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 15, 2016 at 01:55 PM

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 15, 2016 at 02:41 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • Apr 16, 2016 at 02:29 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded