需要有关“关键字 GROUP 附近的语法不正确”的帮助

Question

我不知道我做错了什么。我不断收到不正确的语法错误，似乎我纠正了一个，然后弹出下一行。现在我收到一条“关键字 GROUP 附近的语法不正确。我在这个网站上有几个版本的问题。我真的很感谢到目前为止的帮助。我只需要这种推动来让这个报告工作。任何人都知道任何明尼苏达州的 sql 课程很好。这些书涵盖了基础知识，我需要复杂的 sql 课程。

SELECT fgc, sum(Totpay) as Totpay, Sum(TotChg) as TotChg
from
(                     
    SELECT fgc, sum(pay) as TotPay, 0 as TotChg
    from 
    (
        SELECT fgc, pay,
               CASE 
                   WHEN  [date]<= 30 THEN 'pmt 0-30'      
                   WHEN [date]> 30 AND [date] <= 60 THEN 'pmt 30-60'
                   WHEN [date]> 61 AND [date] <= 90 THEN 'pmt 61-90'
                   WHEN [date]> 91 AND [date] <= 120 THEN 'pmt 91-120'
                   WHEN [date]> 121 AND [date] <= 150 THEN 'pmt 121-150'
                   WHEN [date]> 151 AND [date] <= 180 THEN 'pmt 151-180'
                   ELSE 'pmt 181+' 
               END
        FROM @Pay 
        GROUP BY fgc

        UNION

        SELECT fgc, 0 as TotPay, sum(chg) as TotChg
        from
        (
            SELECT fgc, chg,
                   CASE 
                       WHEN [date]<= 30 THEN 'charge 0-30'      
                       WHEN [date]> 30 AND [date] <= 60 THEN 'charge 30-60' 
                       WHEN [date]> 61 AND [date] <= 90 THEN 'charge 61-90'
                       WHEN [date]> 91 AND [date] <= 120 THEN 'charge 91-120'
                       WHEN [date]> 121 AND [date] <= 150 THEN 'charge 121-150'
                       WHEN [date]> 151 AND [date] <= 180 THEN 'charge 151-180'
                       ELSE 'charge 181+' 
                   END
            FROM @Chg
        )
        GROUP BY fgc 
    )
    GROUP BY fgc

Answer 1

基本上，GROUP BY 关键字用于使用聚合函数的情况，为了使用它，您必须对所有列进行分组，而不对其使用聚合函数。

例如：代替 GROUP BY fgc，应该使用 GROUP BY (fgc, pay, aliasName)。

更改：

选择 fgc，支付，
案件
当 [日期]<= 30 那么 'pmt 0-30'
当[日期]> 30 AND [日期] <= 60 THEN 'pmt 30-60'
当[日期]> 61 AND [日期] <= 90 THEN 'pmt 61-90'
当[日期]> 91 AND [日期] <= 120 THEN 'pmt 91-120'
当[日期]> 121 AND [日期] <= 150 THEN 'pmt 121-150'
当[日期]> 151 AND [日期] <= 180 THEN 'pmt 151-180'
其他“pmt 181+”
结尾
来自@Pay
GROUP BY fgc

进入

SELECT fgc，支付，
案件
当 [日期]<= 30 那么 'pmt 0-30'
当[日期]> 30 AND [日期] <= 60 THEN 'pmt 30-60'
当[日期]> 61 AND [日期] <= 90 THEN 'pmt 61-90'
当[日期]> 91 AND [日期] <= 120 THEN 'pmt 91-120'
当[日期]> 121 AND [日期] <= 150 THEN 'pmt 121-150'
当[日期]> 151 AND [日期] <= 180 THEN 'pmt 151-180'
其他“pmt 181+”
END 作为别名
来自@Pay
GROUP BY (fgc, pay, 别名)

希望有帮助。

P/S：谢谢加布纠正我。

Answer 2

您没有使用别名进行内联查询。以下没有给我任何语法错误。

        SELECT fgc, sum(Totpay) as Totpay, Sum(TotChg) as TotChg
        from
        (                     
        SELECT fgc, sum(pay) as TotPay, 0 as TotChg
        from 
            (SELECT fgc, pay,
                CASE 
                WHEN  [date]<= 30 THEN 'pmt 0-30'      
                WHEN [date]> 30 AND [date] <= 60 THEN 'pmt 30-60'
                WHEN [date]> 61 AND [date] <= 90 THEN 'pmt 61-90'
                WHEN [date]> 91 AND [date] <= 120 THEN 'pmt 91-120'
                WHEN [date]> 121 AND [date] <= 150 THEN 'pmt 121-150'
                WHEN [date]> 151 AND [date] <= 180 THEN 'pmt 151-180'
                ELSE 'pmt 181+' 
                END
            FROM @Pay 
            GROUP BY fgc 

            UNION

            SELECT fgc, 0 as TotPay, sum(chg) as TotChg
            from
                (SELECT fgc, chg,
                    CASE 
                    WHEN [date]<= 30 THEN 'charge 0-30'      
                    WHEN [date]> 30 AND [date] <= 60 THEN 'charge 30-60' 
                    WHEN [date]> 61 AND [date] <= 90 THEN 'charge 61-90'
                    WHEN [date]> 91 AND [date] <= 120 THEN 'charge 91-120'
                    WHEN [date]> 121 AND [date] <= 150 THEN 'charge 121-150'
                    WHEN [date]> 151 AND [date] <= 180 THEN 'charge 151-180'
                    ELSE 'charge 181+' 
                    END
                 FROM @Chg) as innerTable2
             GROUP BY fgc 
            ) As innertable1
        GROUP BY fgc
    )  as outerTable