MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- phpMyAdmin SQL Dump
-- version 4.9.0.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Jan 12, 2020 at 10:12 PM
-- Server version: 5.7.28-cll-lve
-- PHP Version: 7.3.6

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `tposkasa_lametina`
--

-- --------------------------------------------------------

--
-- Table structure for table `simulation_readings`
--

CREATE TABLE `simulation_readings` (
  `id` int(11) NOT NULL,
  `reading_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `reading_temperature` decimal(10,5) NOT NULL,
  `reading_humidity` varchar(255) NOT NULL,
  `reading_pressure` varchar(255) NOT NULL,
  `reading_light` varchar(255) NOT NULL,
  `reading_dewpoint` decimal(10,5) NOT NULL,
  `reading_windspeed` decimal(10,3) NOT NULL,
  `reading_winddirection` varchar(255) DEFAULT NULL,
  `reading_rainfall` varchar(255) NOT NULL,
  `reading_cloudbase` decimal(10,0) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `simulation_readings`
--

INSERT INTO `simulation_readings` (`id`, `reading_datetime`, `reading_temperature`, `reading_humidity`, `reading_pressure`, `reading_light`, `reading_dewpoint`, `reading_windspeed`, `reading_winddirection`, `reading_rainfall`, `reading_cloudbase`) VALUES
(1, '2020-01-06 10:03:49', 5.00000, '30', '1027', '', -11.28344, 9.300, '10', '22', 1985),
(2, '2020-01-06 10:04:49', 5.00000, '30', '1027', '', -11.28344, 9.300, '101', '22', 1985),
(3, '2020-01-06 10:05:49', 5.00000, '30', '1027', '', -11.28344, 9.300, '12', '22', 1985),
(4, '2020-01-06 10:06:50', 5.00000, '30', '1027', '', -11.28344, 9.300, '178', '22', 1985),
(5, '2020-01-06 10:07:50', 5.00000, '30', '1027', '', -11.28344, 9.300, '254', '22', 1985),
(6, '2020-01-06 10:08:50', 5.00000, '30', '1027', '', -11.28344, 9.300, '99', '22', 1985),
(7, '2020-01-06 10:09:50', 5.00000, '30', '1027', '', -11.28344, 9.300, '32', '22', 1985),
(8, '2020-01-06 10:10:51', 5.00000, '30', '1027', '', -11.28344, 9.300, '10', '22', 1985),
(9, '2020-01-06 10:11:51', 5.00000, '30', '1027', '', -11.28344, 9.300, '10', '22', 1985),
(10, '2020-01-06 10:12:51', 5.00000, '30', '1027', '', -11.28344, 9.300, '10', '22', 1985);
--
-- Indexes for dumped tables
--

--
-- Indexes for table `simulation_readings`
--
ALTER TABLE `simulation_readings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `dttam` (`reading_datetime`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `simulation_readings`
--
ALTER TABLE `simulation_readings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9181;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
select 
    case c.windgroup
        when 0 then 'N'
        when 1 then 'NE'
        when 2 then 'E'
        when 3 then 'SE'
        when 4 then 'S'
        when 5 then 'SW'
        when 6 then 'W'
        when 7 then 'NW'
    end windgroup,
    cnt,
    round(c.cnt / t.total * 100) postotak 
from 
    ( 
        select floor(reading_winddirection / 45) windgroup, count(*) cnt
        from simulation_readings  
        group by windgroup
    ) c 
    cross join ( 
        select count(*) total from simulation_readings  
    ) t


windgroup
cnt
postotak


N
6
60

E
2
20

SE
1
10

SW
1
10


select 
    case floor(reading_winddirection / 45)
        when 0 then 'N'
        when 1 then 'NE'
        when 2 then 'E'
        when 3 then 'SE'
        when 4 then 'S'
        when 5 then 'SW'
        when 6 then 'W'
        when 7 then 'NW'
    end windgroup,
    count(*) cnt,
    round(100 * count(*) / sum(count(*)) over()) postotak 
from simulation_readings  
group by windgroup


windgroup
cnt
postotak


N
6
60

E
2
20

SE
1
10

SW
1
10