****************** Raw Report File ****************** -- Overall Leaderboard -- This lists all the products that cost -- more than a given price. USE modena; DROP TABLE if exists allteam; DROP TABLE if exists fish; DROP TABLE if exists chicken; DROP TABLE if exists ribs; DROP TABLE if exists porkshoulder; DROP TABLE if exists brisket; DROP TABLE if exists leaderboard; Create table allteam( teams varchar(30)); Insert into allteam(teams) select team from results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id GROUP BY team; CREATE TABLE fish SELECT team,ROUND(SUM(total),2) as total_fish FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="Fish" group by team; CREATE TABLE chicken SELECT team,ROUND(SUM(total),2) as total_chicken FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="chicken" group by team; CREATE TABLE ribs SELECT team,ROUND(SUM(total),2) as total_ribs FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="Ribs" group by team; CREATE TABLE porkshoulder SELECT team,ROUND(SUM(total),2) as total_porkshoulder FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="PorkShoulder" group by team; CREATE TABLE brisket SELECT team,ROUND(SUM(total),2) as total_brisket FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="Brisket" group by team; create table leaderboard select teams,total_fish,total_chicken,total_ribs,total_porkshoulder,total_brisket, ROUND( coalesce(total_fish,"0.00") +coalesce(total_chicken,"0.00") +coalesce(total_ribs,"0.00") +coalesce(total_porkshoulder,"0.00") +coalesce(total_brisket,"0.00") ,2 ) AS grand_total from allteam left join fish on teams=fish.team left join chicken on allteam.teams=chicken.team left join ribs on allteam.teams=ribs.team left join porkshoulder on allteam.teams=porkshoulder.team left join brisket on allteam.teams=brisket.team group by teams,total_fish,total_chicken,total_ribs,total_porkshoulder, total_brisket order by teams; select * from leaderboard order by grand_total desc, total_ribs desc, total_brisket desc, total_chicken desc, total_porkshoulder desc; ****************** Macros ****************** Array ( ) ****************** All Report Options ****************** Array ( [Filters] => Array ( ) [Variables] => Array ( ) [Includes] => Array ( ) [Name] => Overall Leaderboard [Type] => Pdo [Database] => pdo [Environment] => main [Environments] => Array ( [0] => Array ( [name] => main [selected] => 1 ) ) [Query_Formatted] =>

USE modena; 
DROP 
  TABLE if exists allteam; 
DROP 
  TABLE if exists fish; 
DROP 
  TABLE if exists chicken; 
DROP 
  TABLE if exists ribs; 
DROP 
  TABLE if exists porkshoulder; 
DROP 
  TABLE if exists brisket; 
DROP 
  TABLE if exists leaderboard; Create table allteam(
    teams varchar(30)
  ); Insert into allteam(teams) 
select 
  team 
from 
  results 
  INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id 
GROUP BY 
  team; CREATE TABLE fish 
SELECT 
  team, 
  ROUND(
    SUM(total), 
    2
  ) as total_fish 
FROM 
  results 
  INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id 
where 
  category = "Fish" 
group by 
  team; CREATE TABLE chicken 
SELECT 
  team, 
  ROUND(
    SUM(total), 
    2
  ) as total_chicken 
FROM 
  results 
  INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id 
where 
  category = "chicken" 
group by 
  team; CREATE TABLE ribs 
SELECT 
  team, 
  ROUND(
    SUM(total), 
    2
  ) as total_ribs 
FROM 
  results 
  INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id 
where 
  category = "Ribs" 
group by 
  team; CREATE TABLE porkshoulder 
SELECT 
  team, 
  ROUND(
    SUM(total), 
    2
  ) as total_porkshoulder 
FROM 
  results 
  INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id 
where 
  category = "PorkShoulder" 
group by 
  team; CREATE TABLE brisket 
SELECT 
  team, 
  ROUND(
    SUM(total), 
    2
  ) as total_brisket 
FROM 
  results 
  INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id 
where 
  category = "Brisket" 
group by 
  team; create table leaderboard 
select 
  teams, 
  total_fish, 
  total_chicken, 
  total_ribs, 
  total_porkshoulder, 
  total_brisket, 
  ROUND(
    coalesce(total_fish, "0.00") + coalesce(total_chicken, "0.00") + coalesce(total_ribs, "0.00") + coalesce(total_porkshoulder, "0.00") + coalesce(total_brisket, "0.00"), 
    2
  ) AS grand_total 
from 
  allteam 
  left join fish on teams = fish.team 
  left join chicken on allteam.teams = chicken.team 
  left join ribs on allteam.teams = ribs.team 
  left join porkshoulder on allteam.teams = porkshoulder.team 
  left join brisket on allteam.teams = brisket.team 
group by 
  teams, 
  total_fish, 
  total_chicken, 
  total_ribs, 
  total_porkshoulder, 
  total_brisket 
order by 
  teams; 
select 
  * 
from 
  leaderboard 
order by 
  grand_total desc, 
  total_ribs desc, 
  total_brisket desc, 
  total_chicken desc, 
  total_porkshoulder desc;

[time_estimate] => Array ( [times] => Array ( [0] => 3.38723 [1] => 3.54233 [2] => 3.59111 [3] => 3.62052 [4] => 3.63589 [5] => 3.66769 [6] => 3.70111 [7] => 3.71908 [8] => 3.7903 [9] => 3.98155 [10] => 4.64904 ) [count] => 11 [min] => 3.39 [max] => 4.65 [median] => 3.67 [average] => 3.75 [q1] => 3.62 [q3] => 3.79 [iqr] => 0.25 [sum] => 41.29 [stdev] => 0.32 ) ) ****************** Generated Query ****************** USE modena; DROP TABLE if exists allteam; DROP TABLE if exists fish; DROP TABLE if exists chicken; DROP TABLE if exists ribs; DROP TABLE if exists porkshoulder; DROP TABLE if exists brisket; DROP TABLE if exists leaderboard; Create table allteam( teams varchar(30)); Insert into allteam(teams) select team from results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id GROUP BY team; CREATE TABLE fish SELECT team,ROUND(SUM(total),2) as total_fish FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="Fish" group by team; CREATE TABLE chicken SELECT team,ROUND(SUM(total),2) as total_chicken FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="chicken" group by team; CREATE TABLE ribs SELECT team,ROUND(SUM(total),2) as total_ribs FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="Ribs" group by team; CREATE TABLE porkshoulder SELECT team,ROUND(SUM(total),2) as total_porkshoulder FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="PorkShoulder" group by team; CREATE TABLE brisket SELECT team,ROUND(SUM(total),2) as total_brisket FROM results INNER JOIN dishes_to_team on results.unique_dish_id = dishes_to_team.unique_dish_id where category="Brisket" group by team; create table leaderboard select teams,total_fish,total_chicken,total_ribs,total_porkshoulder,total_brisket, ROUND( coalesce(total_fish,"0.00") +coalesce(total_chicken,"0.00") +coalesce(total_ribs,"0.00") +coalesce(total_porkshoulder,"0.00") +coalesce(total_brisket,"0.00") ,2 ) AS grand_total from allteam left join fish on teams=fish.team left join chicken on allteam.teams=chicken.team left join ribs on allteam.teams=ribs.team left join porkshoulder on allteam.teams=porkshoulder.team left join brisket on allteam.teams=brisket.team group by teams,total_fish,total_chicken,total_ribs,total_porkshoulder, total_brisket order by teams; select * from leaderboard order by grand_total desc, total_ribs desc, total_brisket desc, total_chicken desc, total_porkshoulder desc; ****************** Report Rows ****************** Array ( [0] => Array ( [rows] => Array ( ) [values] => Array ( ) [count] => 0 ) )