急两个有关问题，求大神

急！两个问题，求大神
数据库地址：http://115.com/file/bejzce1g#RDCsetup.sql

1. 编写一个脚本，可以列出每个成员、已付费用、未付费用。将championship和social两类会员分别合计，并给出总和（划分2大类，是否有注册编号，若有，是CM；若没有，是SM。CM,SM分别统计，再给出总和所有的）
championship member和social member的区别是CM有Registrationnum
feespaid是已经付的费用，CM要交185元，SM要交55元
我写的代码是：
SELECT givenname, familyname, feespaid, 185-feespaid as "Fees", sum(feespaid), sum(185-feespaid)
FROM MEMBER
WHERE Registrationnum IS NOT NULL;
GROUP BY givenname, familyname;

总报错

2. FIXTURES表格中列有去年比赛的列表（俱乐部与俱乐部之间有比赛）。核实查证这些fixtures已被正确完成，并编写一个脚本来确认可能存在的问题。你故意去破坏fixtures表格，即数据改错几个地方，来检测这个脚本是否能查到错误。
（一个星期，一个球队比赛一次，比赛6伦，一轮有4个比赛。）
我的算法是：
-四个球队，每个球队每个礼拜都有比赛（若此条成立，则每支球队必然一共打了6场比赛）
-3个客场3个主场
所以结果输出两个表： 1. 计算每周比赛球队数量 2. 计算每个球队的主客场数量
 WEEK HOME AWAY  
---------- ---- ----  
  1 GG HH  
  1 RR SS  
  2 HH RR  
  2 SS GG  
  3 HH SS  
  3 RR GG  
  4 HH GG  
  4 SS RR  
  5 RR HH  
  5 GG SS  
  6 GG RR  
  6 SS HH  


第一个表代码是：
SELECT Weeks, Team,
CASE
WHEN Team=4
THEN 'True'
ELSE 'False'
 END "Check"
FROM
(SELECT x as Weeks, y as Team
from
(select week as x, COUNT(DISTINCT home)+COUNT(DISTINCT away) as y 
FROM fixture
WHERE home<>away
)
GROUP BY x
ORDER BY x);

又报错 说not a single-group group function 崩溃啊






[解决办法]
具体内容没怎么看 分组计算要将单个字段都列出来 才能计算其他函数 2个类型差不多

SQL code

--1SELECT givenname, familyname, feespaid, 185-feespaid as "Fees", sum(feespaid), sum(185-feespaid)FROM MEMBERWHERE Registrationnum IS NOT NULL;GROUP BY givenname, familyname,feespaid;--2select week as Weeks, COUNT(DISTINCT home)+COUNT(DISTINCT away)as Team  FROM fixtureWHERE home<>awayGROUP BY week ORDER BY week
------解决方案-------------------- 
 
SELECT givenname, familyname, feespaid, 185-feespaid as "Fees", sum(feespaid)over(partition by familyname), sum(185-feespaid)over(partition by familyname)
FROM MEMBER
WHERE Registrationnum IS NOT NULL;
可能意图还是理解错误，看着改吧


第二道题，实际上并没有要求主场和客场的场次必须对等吧？
按照题目说的，只要每个星期，每个球队有且只有一场比赛，就ok了
with t1 as (select distinct home team from fixture),
 t2 as (select distinct week from fixture),
 t3 as (select week,home team from fixture union all select week,away from fixture)
select team,max(cnt) as maxcnt,min(cnt) as mincnt from(
select t.team,t.week,count(t3.team) cnt
from (select * from t1,t2) t,t3
where t.team=t3.team(+)
 and t.week=t3.week(+)
group by t.team,t.week
)
group by team
[解决办法]
SQL code
1.SELECT givenname,       familyname,       feespaid,       185 - feespaid as "Fees",       sum(feespaid),       sum(185 - feespaid)  FROM MEMBER WHERE Registrationnum IS NOT NULL;GROUP BY givenname, familyname,feespaid;2.SELECT Weeks,       Team,       CASE         WHEN Team = 4 THEN          'True'         ELSE          'False'       END "Check"  FROM (SELECT x as Weeks, y as Team          from (select week as x,                       COUNT(DISTINCT home) + COUNT(DISTINCT away) as y                  FROM fixture                 WHERE home <> away                 group by week)         GROUP BY x,y         ORDER BY x);