SQL question: How to

Question

Hi!

I have a table 'distance' like this:

Distance || planned_price || actual_price
70 || 13 || 11
120 || 15 || 15
44 || 8 || 12
88 || 10 || 20
.................

Is it possible to get the output like:

Distance || 1-5 higher || 6-10 higher || in range || below range
under 50 || 25% || || ||
50-100 || || 25% || || 25%
over 100 || || || 25% ||

I need to compare 'actual_price' with 'planned_price' and defined it is higher, below, or in range. Then group by distance and count %.

But i have no idea how to query it and even if possible is it or not?

Answer 1

On the one hand you have to group the data of the Distance column in the ranges with a case statement.

On the other hand, the different columns must be constructed by counting when the condition is satisfied and dividing each one by the total count of the table to obtain the percentage data.

The query could look something like this:

select case when Distance < 50 then 'under 50'
when Distance between 50 AND 100 then '50-100'
else 'over 100'
end as Distance,
count(case when actual_price-planned_price between 1 and 5 then 1 end)/(select count(*) from distance) '1-5 higher',
count(case when actual_price-planned_price between 6 and 10 then 1 end)/(select count(*) from distance) '6-10 higher',
count(case when actual_price-planned_price = 0 then 1 end)/(select count(*) from distance) as 'in range',
count(case when actual_price-planned_price < 0 then 1 end)/(select count(*) from distance) as 'below range'
from distance
group by 1;

Answer 2

you can get the comparison part with a calculated column, in your select
SELECT distance, planned_price / actual_price AS comparisonColumn .. FROM..

other than that break the rest down into parts chaining as much as you can together with a WHERE, GROUPBY, and maybe AVG.

It's definitely not going to be the shortest query so take your time and get the results in parts if you have to, to make it easier.

The final result should look similar to the sentence you wrote down once you translate it into sql.