How to make case in HANA
25,017
You may change to this:
SELECT range,
COUNT(*) AS vehicle_count
FROM (
SELECT (CASE WHEN meter >= 0 AND meter< 10000 THEN '0-10'
WHEN meter >= 10000 AND meter < 20000 THEN '10-20'
WHEN meter >= 20000 AND meter <30000 THEN '20-30'
WHEN meter >= 30000 AND meter < 40000 THEN '30-40'
WHEN meter >= 40000 AND meter < 50000 THEN '40-50'
WHEN meter >= 50000 AND meter < 60000 THEN '50-60'
WHEN meter >= 60000 AND meter <70000 THEN '60-70'
WHEN meter >= 70000 AND meter < 80000 THEN '70-80'
WHEN meter >= 80000 AND meter < 100000 THEN '80-90'
WHEN meter >=100000 THEN 'above 100'
END) AS range
FROM (
SELECT to_integer(to_varchar(time,'DDMMYYYY')) AS day,
--You should considering using other way to truncate date from time,
--not convert too much,
--at least you could use only to_varchar is enough
place,
vehicle_id,
SUM(meter_two_points) AS meter
FROM public.datatable
WHERE time >= '2015-09-05 00:00:00'
AND time <= '2015-09-05 23:00:00'
AND place=10
GROUP BY to_integer(to_varchar(time,'DDMMYYYY')),
vehicle_id,
place
)
) AS a
GROUP BY range
ORDER BY range
LIMIT 10; --why limit 10 here while you only have 10 ranges?
And as SQL order of operations, I keep wondering if PostgreSQL could GROUP BY
after SELECT
so group by could group with range column in your first query.
Author by
Admin
Updated on July 07, 2022Comments
-
Admin almost 2 years
I have the following query in postgres which gives the meter range and the number of vehicles travelled between the range
SELECT (CASE WHEN meter >= 0 AND meter< 10000 THEN '0-10' WHEN meter >= 10000 AND meter < 20000 THEN '10-20' WHEN meter >= 20000 AND meter <30000 THEN '20-30' WHEN meter >= 30000 AND meter < 40000 THEN '30-40' WHEN meter >= 40000 AND meter < 50000 THEN '40-50' WHEN meter >= 50000 AND meter < 60000 THEN '50-60' WHEN meter >= 60000 AND meter <70000 THEN '60-70' WHEN meter >= 70000 AND meter < 80000 THEN '70-80' WHEN meter >= 80000 AND meter < 100000 THEN '80-90' WHEN meter >=100000 THEN 'above 100' END) as range,count(*) as vehicle_count from ( SELECT extract (day from time) as day, place,vehicle_id,sum(meter_two_points) as meter FROM public.datatable where time >= '2015-09-05 00:00:00' and time <= '2015-09-05 23:00:00' and place=10 group by day,vehicle_id,place order by day,vehicle_id ) as A group by range order by range limit 10
I now want to do the same query for the same table in HANA.So I tried with
SELECT (CASE WHEN meter >= 0 AND meter< 10000 THEN '0-10' WHEN meter >= 10000 AND meter < 20000 THEN '10-20' WHEN meter >= 20000 AND meter <30000 THEN '20-30' WHEN meter >= 30000 AND meter < 40000 THEN '30-40' WHEN meter >= 40000 AND meter < 50000 THEN '40-50' WHEN meter >= 50000 AND meter < 60000 THEN '50-60' WHEN meter >= 60000 AND meter <70000 THEN '60-70' WHEN meter >= 70000 AND meter < 80000 THEN '70-80' WHEN meter >= 80000 AND meter < 100000 THEN '80-90' WHEN meter >=100000 THEN 'above 100' END) as range,count(*) as vehicle_count from ( But it throws with the error `invalid column name range` SELECT to_integer(to_varchar(time,'DD')) as day, place,vehicle_id,sum(meter_two_points) as meter FROM public.datatable where time >= '2015-09-05 00:00:00' and time <= '2015-09-05 23:00:00' and place=10 group by day,vehicle_id,place order by day,vehicle_id ) as A group by range order by range limit 10
-
Pham X. Bach about 7 yearsThat postgre should throws that error, too. As order of operation in SQL in general, you can't group like that. At least you need a nest subquery
-
Admin about 7 yearsNo in postgres it works fine
-
-
Admin about 7 yearsYup it was all about the SQL order of operations where PostgreSQL could GROUP BY after SELECT but HANA could not do it.Cheers