无法从调查中找出这个SQL

Question

必须有一种更简单的方法来做到这一点，但我在SQL方面非常新。我从一次大规模的调查中得到了这些数据，我需要帮助。我想从每堂课中找出家庭作业的平均时间和总时间，但数据被格式化为所有年级都在一个表格中。应该没那么难，但我想不出来。谢谢!

SELECT
  What_grade_are_you_in as Grade,
  How_much_free_time_do_you_have_each_night as FreeTime,
  AvgEnglish9HW,
  AvgBible9HW,
  AvgGov9HW,
  AvgMath9HW,
  AvgScience9HW,
  AvgLang9HW,
  AvgArt9HW,

  AvgEnglish10HW,
  AvgBible10HW,
  AvgGov10HW,
  AvgMath10HW,
  AvgScience10HW,
  AvgLang10HW,
  AvgArt10HW,

  AvgEnglish11HW,
  AvgBible11HW,
  AvgGov11HW,
  AvgMath11HW,
  AvgScience11HW,
  AvgLang11HW,
  AvgArt11HW,

  AvgEnglish12HW,
  AvgBible12HW,
  AvgGov12HW,
  AvgMath12HW,
  AvgScience12HW,
  AvgLang12HW,
  AvgArt12HW,

  SUM(AvgEnglish12HW, AvgBible12HW, AvgGov12HW, AvgMath12HW, AvgScience12HW, AvgLang12HW, AvgArt12HW) as TotalHW12,
  SUM(AvgEnglish11HW,AvgBible11HW,AvgGov11HW,AvgMath11HW,AvgScience11HW,AvgLang11HW,AvgArt11HW) as TotalHW11,
  SUM(AvgEnglish10HW,AvgBible10HW,AvgGov10HW,AvgMath10HW,AvgScience10HW,AvgLang10HW,AvgArt10HW) as TotalHW10,
  SUM(AvgEnglish9HW,AvgBible9HW,AvgGov9HW,AvgMath9HW,AvgScience9HW,AvgLang9HW,AvgArt9HW) as TotalHW9
FROM `omnihealth-1.HWLoad.HWLoad` ,(
SELECT
AVG(On_average_how_much_homework_do_you_have_per_class_from_English) as AvgEnglish9HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Bible) as AvgBible9HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Gov) as AvgGov9HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Math) as AvgMath9HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Science) as AvgScience9HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Foreign_Language) as AvgLang9HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Fine_Arts) as AvgArt9HW
FROM `omnihealth-1.HWLoad.HWLoad` 
WHERE What_grade_are_you_in = 9 

SELECT
  AVG(On_average_how_much_homework_do_you_have_per_class_from_English) as AvgEnglish10HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Bible) as AvgBible10HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Gov) as AvgGov10HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Math) as AvgMath10HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Science) as AvgScience10HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Foreign_Language) as AvgLang10HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Fine_Arts) as AvgArt10HW
FROM `omnihealth-1.HWLoad.HWLoad` 
WHERE What_grade_are_you_in = 10 

SELECT
AVG(On_average_how_much_homework_do_you_have_per_class_from_English) as AvgEnglish11HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Bible) as AvgBible11HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Gov) as AvgGov11HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Math) as AvgMath10HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Science) as AvgScience11HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Foreign_Language) as AvgLang11HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Fine_Arts) as AvgArt11HW
FROM `omnihealth-1.HWLoad.HWLoad` 
WHERE What_grade_are_you_in = 11

SELECT
AVG(On_average_how_much_homework_do_you_have_per_class_from_English) as AvgEnglish12HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Bible) as AvgBible12HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Gov) as AvgGov12HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Math) as AvgMath12HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Science) as AvgScience12HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Foreign_Language) as AvgLang12HW,
  AVG(On_average_how_much_homework_do_you_have_per_class_from_Fine_Arts) as AvgArt12HW
FROM `omnihealth-1.HWLoad.HWLoad` 
WHERE What_grade_are_you_in = 12
)  

GROUP BY
Grade
ORDER BY
TotalHW12,TotalHW11,TotalHW10,TotalHW9 DESC

Answer 1

注意:您的输出(在您的问题中的示例的外部选择语句中)根本没有意义！从一边，它是按等级分组，但从另一边，你有所有的分数在同一排！一点意义都没有！所以我继续，假设产出低于预期。

它适用于BigQuery标准SQL

#standardSQL
SELECT
  Grade,
  AvgEnglish,
  AvgBible,
  AvgGov,
  AvgMath,
  AvgScience,
  AvgLang,
  AvgArt,
  AvgEnglish + AvgBible + AvgGov + AvgMath + AvgScience + AvgLang + AvgArt AS Total
FROM (
  SELECT
    What_grade_are_you_in AS Grade
    AVG(On_average_how_much_homework_do_you_have_per_class_from_English) AS AvgEnglish,
    AVG(On_average_how_much_homework_do_you_have_per_class_from_Bible) AS AvgBible,
    AVG(On_average_how_much_homework_do_you_have_per_class_from_Gov) AS AvgGov,
    AVG(On_average_how_much_homework_do_you_have_per_class_from_Math) AS AvgMath,
    AVG(On_average_how_much_homework_do_you_have_per_class_from_Science) AS AvgScience,
    AVG(On_average_how_much_homework_do_you_have_per_class_from_Foreign_Language) AS AvgLang,
    AVG(On_average_how_much_homework_do_you_have_per_class_from_Fine_Arts) AS AvgArt
  FROM `omnihealth-1.HWLoad.HWLoad` 
  WHERE What_grade_are_you_in IN (9, 10, 11, 12)
  GROUP BY 1
)  
ORDER BY Total DESC