PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
with d as ( select DATE_FORMAT(rental_date, '%w') d, DATE_FORMAT(rental_date, '%W') day_of_week, COUNT(*) rentals_count from rental group by DATE_FORMAT(rental_date, '%w'), DATE_FORMAT(rental_date, '%W') ) select day_of_week, rentals_count , (rentals_count / sum(rentals_count) over ()) * 100 day_rental_rate from d order by d = 0, d;
Copy Clear
Copy Format Clear
<?php $query = "select title, film_id, avg(datediff(return_date, rental_date) - rental_duration) average_delay from rental join inventory using(inventory_id) join film using(film_id) where datediff(return_date, rental_date) > rental_duration group by title, film_id having avg(datediff(return_date, rental_date) - rental_duration) > 5;"; // get DB version using PDO $stmt = $pdo->prepare($query); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_NUM); $columns = []; for ($i = 0; $i < $stmt->columnCount(); $i++) { $col = $stmt->getColumnMeta($i); $columns[] = [ 'header'=>$col['name'], 'pdo_type'=>$col['pdo_type'] ]; } echo json_encode([[ 'headers' => $columns, 'data' => $rows ]], JSON_PRETTY_PRINT );
Show:  
Copy Clear