CODEWITHSUNDEEP
sqlapache-sparkapache-spark-sql
Ankit Khanna
Ankit Khanna

Reputation: 23

The table returns column number instead of count

I'm using this query but it returns column numbers instead of the count count(bedroom_count):

select
  states,
  0,
  1,
  2,
  3,
  4,
  5,
  6,
  7,
  8,
  9,
  10
from
  (
    select
      states,
      bedroom_count
    from
      hive_metastore.property_db_dev.datatree_assessor_silver
  ) PIVOT (
    count(bedroom_count) FOR bedroom_count IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
  )
ORDER BY
  states;

Upvotes: 0

Views: 178

Answers (1)

blackbishop
blackbishop

Reputation: 32640

You need to use back-ticks to escape the column names otherwise Spark is interpreting them as integer literals in your query:

SELECT states, `0`, `1`, `2`, `3`, `4`, `5`, `6`, `7`, `8`, `9`, `10`
FROM (
   SELECT states,
          bedroom_count
   FROM   hive_metastore.property_db_dev.datatree_assessor_silver 
) 
PIVOT (
   count(bedroom_count) FOR bedroom_count IN (0,1,2,3,4,5,6,7,8,9,10)
)
ORDER BY states;

Or simply use select * if you want all the pivoted columns:

SELECT *
-- ...
ORDER BY states;

Upvotes: 1

Related Questions